mijwil
asked on
Date() in string
I apologize for the stupidity of this question. What is the proper syntax in using the Date() function in VBA. I have a shipping form that I want to filter what shipped today.
In a query I know its simply =Date() - In VBA ; quotation marks etc. I'm a little foggy.
'Today Button filtering SH_ShipDate
Dim today As String
today = "SH_ShipDate = date()"
Me.Filter = today
Me.FilterOn = True
Thanks for your help
In a query I know its simply =Date() - In VBA ; quotation marks etc. I'm a little foggy.
'Today Button filtering SH_ShipDate
Dim today As String
today = "SH_ShipDate = date()"
Me.Filter = today
Me.FilterOn = True
Thanks for your help
Try this:
Dim today As String
today = "SH_ShipDate = #" & date() & "#"
Me.Filter = today
Me.FilterOn = True
By no means am I a VBA expert but I have done something similar.
Today = "SH_ShipDate = " & Date()
Today = "SH_ShipDate = " & Date()
My previous comment is assuming that ShipDate is stored as a Date field in your Access database. For a date field, your filter needs # signs as delimiters:
If you are storing it as text, you would need single quote delimiters:
Dim today As String
today = "SH_ShipDate = #" & date() & "#"
Me.Filter = today
Me.FilterOn = True
If you are storing it as text, you would need single quote delimiters:
Dim today As String
today = "SH_ShipDate = '" & date() & "'"
Me.Filter = today
Me.FilterOn = True
ASKER
Odd thing. If "date()" is not in quotes VBA is removing the (). Leaving this:
"SH_ShipDate = ' " & Date & " ' "
It is removing the () for all of the above recommendations and none are working.
"SH_ShipDate = ' " & Date & " ' "
It is removing the () for all of the above recommendations and none are working.
Try either:
today = "SH_ShipDate = '" & Format(Now(), "MM/dd/yyyy") & "'"
or
today = "SH_ShipDate = """ & Format(Now(), "MM/dd/yyyy") & """"
today = "SH_ShipDate = '" & Format(Now(), "MM/dd/yyyy") & "'"
or
today = "SH_ShipDate = """ & Format(Now(), "MM/dd/yyyy") & """"
ASKER
Looking to do something similar in code for looking at shipping the past 7 days.
in a query it's >=Date()-7.
Can't get the hang of the apersands, quotes, etc.
in a query it's >=Date()-7.
Can't get the hang of the apersands, quotes, etc.
VBA Dropping the () is fine... it will evaluate to todays date, and apply that to your filter.
What were the results when you tried this?
What were the results when you tried this?
Dim today As String
today = "SH_ShipDate = #" & date() & "#"
Me.Filter = today
Me.FilterOn = True
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
mbizup - returned no results.
today = "SH_ShipDate >= #" & Format(DateAdd("d", -7, Now()), "MM/dd/yyyy") & "#"
ASKER
mbizup - your last post worked for todays date.
I can play with the syntax to get the other results I need.
Well done. Thanks.
I can play with the syntax to get the other results I need.
Well done. Thanks.
-->returned no results.
In that case, you have no records where this field is EXACTLY equal to today's date (are you using a date/time field?). Try my last comment, for the past 7 days, and also try this for an exact match:
In that case, you have no records where this field is EXACTLY equal to today's date (are you using a date/time field?). Try my last comment, for the past 7 days, and also try this for an exact match:
Dim today As String
today = "Format(SH_ShipDate, 'yyyymmdd') >= '" & format(date(),'yyyymmdd') & "'"
Me.Filter = today
Me.FilterOn = True
ASKER
>= seems to have been the missing link. I don't know why. It worked.
ASKER
>= worked ; = did not work. It is formatted as a short date format. Odd - but it worked.
today = "SH_ShipDate = " & Format(Now(), "MM/dd/yyyy")