MS Access sql and period date separators

Hi

I am working on an MS Access db which is to be used in Lithuania where the standard date format is yy.mm.dd (thus the system date format is set to this). One problem I am having is when using sql statements in VBA where the criteria is a date I get an error as SQL doesnt seem to like period separators in dates.

For example if I use the following (myVarDate gets it's value from the Date Picker on a form)

me.recordsource = "Select * from MyTable where MyDate = #" & myVarDate & "#"


If myVarDate = 13.09.27 the statement causes an error

However

If myVarDate  = 13/09/27 then it is OK

I've tried formatting the date to force forward slash separators but this still returns period separators.  The only solution I've found is to use the Replace function to change the periods into slashes and this works.

However I just wondered if anyone had any experience of this and had found any better solutions.

many thanks
kenabbottAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Brute force:

Cetate a query:
SELECT YourTable.*, CDate(Replace(CStr([Mydate]),".","/")) AS NewDate
FROM YourTable;

..Then use this query in the recordsource instead of the table:

Something like this perhaps?

me.recordsource = "Select * from MyQuery where NewDate= #" & myVarDate & "#"

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
;-)

...I may have gotten that backwards...

You can also convert the MyDate variable in the same way:

MyDate=CDate(Replace(CStr([Mydate]),".","/"))
Then it should work as you had it originally:

me.recordsource = "Select * from MyTable where MyDate = #" & myVarDate & "#"

But I am sure you se what I am doing in both cases, ...convecting the dates with periods to  dates with slashes

;-)

JeffCoachman
0
Gustav BrockCIOCommented:
You know better, Jeff.

me.recordsource = "Select * from MyTable where MyDate = #" & myVarDate & "#"

You should always format the date variable to a string expression. If not, the variable will be casted to a string using the local settings which - as in this case - may not be valid SQL syntax. Also the ISO sequence - yyyy/mm/dd - should be used.
Thus, and using the backslash as the escape character because the slash has to be literal:

strDate = Format(myVarDate, "yyyy\/mm\/dd")
Me.RecordSource = "Select * From MyTable Where MyDate = #" & strDate & "#"

/gustav
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

kenabbottAuthor Commented:
I tried

strDate = Format(myVarDate, "yyyy\/mm\/dd")

but strDate is still returned with period separators
0
Gustav BrockCIOCommented:
Then myVarDate is not of data type Date.
If you cannot correct this, you must convert:

strDate = Format(DateValue(myVarDate), "yyyy\/mm\/dd")

/gustav
0
kenabbottAuthor Commented:
Many thanks

I am now having the same problem with currency and numbers where the separator is a comma which again is throwing any SQL statements.  Is there a similar solution for these?
0
Gustav BrockCIOCommented:
Yes. Those you convert to string expressions using Str which always uses a dot for the decimal separator:

curAmount = 1234 / 100    ' 12,34
strAmount = Str(curAmount)    ' 12.34

Format() won't work here.

/gustav
0
Jeffrey CoachmanMIS LiasonCommented:
<You know better, Jeff.>
LOL, yes, that is why I stated:
    "Brute force:"
...in my fist post...
;-)

I make no secrets about my "Hammer" approach to many issues...
;-)

My hope was that this quick and dirty fix might get the OP up and going until another expert came along with something more elegant...

I try to use the ISO date sometimes as well, but I try to use the ISO 8601 variant:
YYYY-MM-DD  ...because that is what SQL likes (I think)

Finally I was unsure of the consequences of doing this with a different regional setting...

;-)

JeffCoachman
0
Gustav BrockCIOCommented:
Ha ha! Nothing like brute force.

I think you can get away with yyyy-mm-dd as well in most cases, but Access SQL likes the slash as the date separator.

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.