Link to home
Start Free TrialLog in
Avatar of mijwil
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
Avatar of dsacker
dsacker
Flag of United States of America image

You may want to do something like:

today = "SH_ShipDate = " & Format(Now(), "MM/dd/yyyy")
Try this:

Dim today As String
today = "SH_ShipDate = #" & date()  & "#"

Me.Filter = today
Me.FilterOn = True

Open in new window

By no means am I a VBA expert but I have done something similar.

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:

Dim today As String
today = "SH_ShipDate = #" & date()  & "#"

Me.Filter = today
Me.FilterOn = True

Open in new window


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

Open in new window

Avatar of mijwil
mijwil

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.
Try either:

today = "SH_ShipDate = '" & Format(Now(), "MM/dd/yyyy") & "'"

or

today = "SH_ShipDate = """ & Format(Now(), "MM/dd/yyyy") & """"
Avatar of mijwil

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.
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?

Dim today As String
today = "SH_ShipDate = #" & date()  & "#"

Me.Filter = today
Me.FilterOn = True

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of mbizup
mbizup
Flag of Kazakhstan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mijwil

ASKER

mbizup - returned no results.
today = "SH_ShipDate >= #" & Format(DateAdd("d", -7, Now()), "MM/dd/yyyy") & "#"
Avatar of mijwil

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.
-->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:

Dim today As String
today = "Format(SH_ShipDate, 'yyyymmdd') >=  '" & format(date(),'yyyymmdd')  & "'"

Me.Filter = today
Me.FilterOn = True

Open in new window

Avatar of mijwil

ASKER

>= seems to have been the missing link. I don't know why. It worked.
Avatar of mijwil

ASKER

>= worked ; = did not work. It is formatted as a short date format. Odd - but it worked.