Source language: Translate to:

Query for Month

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Query for Month

Postby BRobinsonS » Sun Aug 24, 2014 4:27 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Query for Month

Postby Gaev » Sun Aug 24, 2014 5:57 pm

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]"
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Query for Month

Postby BRobinsonS » Mon Aug 25, 2014 6:30 am

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).
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Query for Month

Postby BRobinsonS » Mon Aug 25, 2014 7:00 am

I may have found my solution using the _ (underscore) wildcard.
Pays to read the help file a little closer.
Haven't used underscore before.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Query for Month

Postby Gaev » Mon Aug 25, 2014 11:00 am

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.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Query for Month

Postby stu » Tue Sep 02, 2014 7:05 pm

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.)
Microsoft Access hater.
User avatar
stu
 
Posts: 318
Joined: Wed Aug 07, 2013 11:37 am


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests