UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Show Upcoming Birthdays, Any Version    
 
   
mrdrjay
post Mar 12 2018, 05:10 AM
Post#1



Posts: 70
Joined: 27-June 14



Good Day,

I am currently trying to code a way to show a list of names that has birthdays this month and next month and for them to show in a Msgbox. The below is what I have so far and when ran I get a run-time error '3075':

Syntax error(missing operator) in query expression '[date of birth] = BETWEEN ## And ##. Request your assistance with this. Thanks

CODE
Dim dteBirthDate As Date





'Work on dteBirthDate = Dlookup("[name]", "Training_Update", "[date of birth] = #" & Format([Date of Birth], "05/08/1979") & "#")
dteBirthDate = DLookup("[name]", "Training_Update", "[date of birth] = BETWEEN #" & Format(M, "1/01/2000") & "# And #" & Format(M, "06/30/2010") & "#")
MsgBox dteBirthDate



Go to the top of the page
 
cheekybuddha
post Mar 12 2018, 05:42 AM
Post#2


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


Your Format() expression is wrong.

Also, BETWEEN is an operator like '='. You can't use both.

It should be:
CODE
' ...
dteBirthDate = DLookup("[name]", "Training_Update", "[date of birth] BETWEEN #" & Format("1/01/2000", "yyyy\-mm\-dd") & "# And #" & Format("06/30/2010", "yyyy\-mm\-dd") & "#")

However, since you are hardcoding the dates you could more simply use:
CODE
' ...
dteBirthDate = DLookup("[name]", "Training_Update", "[date of birth] BETWEEN #2000-01-01# And #2010-06-30#")


But then ...

You need to also note that DLookup will return only the 'first' match that it finds. (Since you don't define a sort order, 'first' can be arbitrary, and not the record you may think).

If there are more matches for the date period (and I guess with such a large window that there will be), then you might need to use a recordset instead, and iterate over the results.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 12 2018, 05:50 AM
Post#3


UtterAccess VIP
Posts: 10,139
Joined: 6-December 03
From: Telegraph Hill


Another note:

dteBirthDate sounds like it's a variable declared As Date.

If so, then your DLookup() seems to be returning a name (I assume a string) and you will probably get a Datatype error when trying to assign it to a date.

Best to use a Variant variable to take the result of a DLookup(), because if there no matches it will return Null.

(Might be academic, if you really need to use a recordset!)

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th July 2018 - 10:59 PM