Page 1 of 1

Query for Month

PostPosted: Sun Aug 24, 2014 4:27 pm
by BRobinsonS
How would I do a query on a text field contain a date of format mm/dd/yyyy where I only want the month portion?

I have a members database and want to report members for a particular month to include in a newsletter i.e. September=09 or October = 10
I had to use a Text field in stead of Date field because I didn't and don't have everyone's birthday.
Sometimes only the Year of birth.

Re: Query for Month

PostPosted: Sun Aug 24, 2014 5:57 pm
by Gaev
Brian:

a text field contain a date of format mm/dd/yyyy where I only want the month portion
September=09 or October = 10

I had to use a Text field in stead of Date field because I didn't and don't have everyone's birthday.
Sometimes only the Year of birth.

Let us say the field is called Birthdate ... and the variable containing the month to be searched is called [SearchMonth].

You don't say how the partial dates are stored ...

a) If all dates are stored as 10 characters like ...

21/10/2002
xx/10/2002
xx/xx/2002

... then ...
Code: Select all
dbpQuery "myDB" "myPeoples" "!Birthdate LIKE [#34]%/[SearchMonth]/%[#34]"


b) If dates are stored like ...

21/10/2002
10/2002
2002

... then it becomes difficult to separate day 10 from month 10 ... however, assuming all myPeoples records only have years 19xx or 20xx ...
Code: Select all
dbpQuery "myDB" "myPeoples" "!Birthdate LIKE [#34]%/[SearchMonth]/%[#34] OR Birthdate LIKE [#34][SearchMonth]/19%[#34] OR Birthdate LIKE [#34][SearchMonth]/20%[#34]"

Re: Query for Month

PostPosted: Mon Aug 25, 2014 6:30 am
by BRobinsonS
I have been using the format MM/DD/YYYY.
So for missing information I am using ??/??/2002 or 09/??/2002 or even ??/??/???? (for no birthday information)

My attempt for September query picked up i.e. 09/11/2002 but also date like 02/09/2003.

Tried query using
dbpQuery "myDB" "myPeoples" "!Birthdate LIKE [#34]%[SearchMonth]/%[#34]"

i.e Search month 09 without the ! (I forget why ! is used).

Re: Query for Month

PostPosted: Mon Aug 25, 2014 7:00 am
by BRobinsonS
I may have found my solution using the _ (underscore) wildcard.
Pays to read the help file a little closer.
Haven't used underscore before.

Re: Query for Month

PostPosted: Mon Aug 25, 2014 11:00 am
by Gaev
Brian:
I have been using the format MM/DD/YYYY.
My attempt for September query picked up i.e. 09/11/2002 but also date like 02/09/2003.

Oops ... I was thinking like a Canadian ... missed that you used American format dates.

So for missing information I am using ??/??/2002 or 09/??/2002 or even ??/??/???? (for no birthday information)

So, the query can be ...
Code: Select all
dbpQuery "myDB" "myPeoples" "!Birthdate LIKE [#34][SearchMonth]/%[#34]

... since all you are looking for is values that start with the selected month e.g. 09/ (for September).

I may have found my solution using the _ (underscore) wildcard.
Pays to read the help file a little closer.
Haven't used underscore before.
Definitely a great find ... I always wondered why there wasn't a wildcard specifier for a single character ... like we used to have in good ole DOS ... like dir image???.*

From the Help file ...
Another wildcard is the underscore character (_) which can be used to represent any single character. For example:
LastName LIKE "sm_th"


Thanks for the tip.

Re: Query for Month

PostPosted: Tue Sep 02, 2014 7:05 pm
by stu
Maybe my approach its totally irrelevant but I manage to solve a similar problem by adding an extra field which contains the result of a "DateToNum" vs the selected date... this is very useful (in my case) to determine a number of things (age, days since, is able to, licence exp, etc.)