Hi,

Doctor9 posted/attached recently a very useful calendar in the Code Archive. It marries well into my application.

I have converted the db to Access 2000, but when opening the form "Calendar_Daily" I get a syntax error message, saying that in tbl_apointmens.apptdate =#01.05.2008#. I use the european date format.

Have tried several soulutions, but am unable to resolve the issue.

Any pointers? Would appreciate an help.

Thanks, Luigisoft

See if anything in this link helps…

It’s been there for a while but is still valid.

Regards,
Chris.

Luigisoft,

Gosh, I could've sworn we had gotten the last international date issue taken care of.

Can you be more specific about your error message? Where, in the VBA code, does the error occur? I'm assuming you can hit the [debug] button to step through the code once the error occurs.

Dennis

Dennis,

On debug in the following VBA code:

Public Sub DisplayDailyMeetings()

on the code line

Set rst = CurrentDb.OpenRecordset(strSQL)

The runtime error number is 3075.
The following references are ticked:
Visual basic for applications
Microsoft Access 9.0 object library
OLE Automation
Microsoft ActiveX data 2.1 objects library
Microsoft visual basic for applications exstensibility 5.3

hth for further troubleshooting.

Trevor

Dennis,

I have continued searching for the reason and it definitely is connected with the american date format. As a trial, I changed my regional settings in the PC to USA and the problem goes away.
However, I need to return to the German date format.

I went to the site as suggested by ChrisO and Allen Browne has come up with what looks like a solution. It is:

Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. allen@allenbrowne.com, June 2006.
If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

Could this be a solution ? Any suggestions as to how the code could then be integrated into your application, if applicable?

Many thanks.

Trevor

Dennis,

I believe to have found the solution.

I returned to the German date format setting in the regional setting and changed the date format from long to short.

Violà, the calendar functions without problems. Useful information for users out there perhaps.
This is good for home use. However, if I transfer my application to my work environment, I get the same problem, as my PC at the desk is protected and I cannot change the regional settings.

Is there a way of getting around this in VBA?

As ever, appreciate very much your help.

Trevor.

I am unable to change my regional settings here to test this, so I hope I don't come across as too bossy. This shouldn't be TOO bad, theoretically. It looks like Allen Browne is suggesting that we replace our date field with his custom function.

1. Create a new code module in the Modules tab of the database window.
2. Name the module something DIFFERENT from the function name, like "modDateFixer".
3. Paste Allen's function code into the module.
4. Change the SQL portion of the DisplayDailyMeetings subroutine to this:


That's all I think needs to be done. (I've done this on my end, and it still seems to work the same, which is good.)

If it doesn't work, E-Mailing Allen would probably be the safest bet, if I'm off-track here. But I feel pretty confident about this. Please let me know either way, though. If I can incorporate this into an update, and be certain that it works overseas, it'd be very useful!

Good luck!

Dennis

Dennis,

Many thanks for your time and patience.
I have incorperated the code and will try it out at work tomorrow. Will let you know how I get on.

Have a good day.

Trevor

Just to be thorough, if I'm right, we probably need to wrap the date inside this function in the following places:

frmAppointments - On Open event:

frmCalendar_Daily DisplayDailyMeetings subroutine:

Hope this helps,

Dennis

I thought the whole thing was explained quite clearly, is there still any confusion?

Dennis, was the calendar ever tested with different regional settings?
Perhaps if I could see how it was tested I might be able to offer a reason the failure did not arise.

Regards,
Chris.

Hi guys,

Had a look this morning at work and modifying the code as Chris suggested has remedied the problem also on my work computer.
I have gone through all (hopefully) possibilities the calendar application has to offer and all worked without problems.

Many thanks again for your valuable help. It's good to know we are not alone when we are faced with Access difficulties.

have a good day.
Trevor.

Trevor,

Thanks so much for the bug testing and the feedback! I'm glad you got it working!

Chris,

Back when a user in Australia first spurred me to try to fix the appointment calendar, I contacted him when I thought it was done, and asked if there were still any errors. He didn't get back to me, so I assumed that (News Is Null) = (News = "good"). I was unaware of the SQL glitch at the time, and when I first saw the calendar, it was using string functions to parse out the month/day/year segments of the date. I assumed that replacing those with date functions would be enough to fix any international issues. Now I know better.

As far as confusion goes, I was just showing Trevor how to implement the Lebans UDF into the existing code. Looks like the code archive needs to be updated once again. I'll go to the Lebans website to get the original function code, then I'll add some comments to it once it's added to the appointment database, giving credit where credit is due.

Dennis

Because of the diversity of people that can download material from the code archive it becomes the hardest of taskmasters.
That is why I now prefer to air it in that other forum for testing before submitting it to the archive.

Regards,
Chris.