Solved

Syntax error when trying to open report in Access 2007

Posted on 2009-04-06
36
354 Views
Last Modified: 2013-11-28
Hi experts,

I want to open a report showing records between two dates. I have a form where I input the dates by choosing in the calendar that pops - up. I have the following code:

Private Sub cmdViewReport_Click()
DoCmd.OpenReport "rptKondisjon", acViewPreview, , "TheDate Between " & "#" & Me.txtStart & "#" & " AND " & "#" & Me.txtSlutt & "#"
End Sub

However, when I click the following error message pops up.

"Syntax error in date in query expression" Please see attachment for further details.

Date format: 07.04.2009 means 7th of April 2009. This format is what comes up when I choose the date in the calendar, and also is the format that is displayed in the table.

Please suggest me how to solve this.

Best regards,

Stayer
screenshot.bmp
0
Comment
Question by:Stayer
  • 15
  • 12
  • 7
  • +1
36 Comments
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
I thought msaccess understood dates with .
how is the date stored in TheDate, is that also with a .?

I wonder if its worth changing . to /

Private Sub cmdViewReport_Click()
DoCmd.OpenReport "rptKondisjon", acViewPreview, , "TheDate Between " & "#" & replace(Me.txtStart,".","/") & "#" & " AND " & "#" & replace(Me.txtSlutt,".","/") & "#"
End Sub

or maybe use the format command instead
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
You will need to replace the . with /, convert the text to a date then format it as MM/DD/YYYY, I have used a variable in my example

Cheers, Andrew

Dim strWHERE as String
strWHERE = "TheDate Between " & "#" & Format(CDate(replace(Me.txtStart,".","/"),"MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(replace(Me.txtSlutt,".","/"),"MM/DD/YYYY") & "#"
DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
rockiroads,

In the interest of full disclosure I will say that I created this file for the asker in a previous question.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Reports/Q_24288336.html

I was uncertain if Access would correctly interpret the dates if the Country in which computer is set up for, was not USA.

I am looking forward to finding out what the resolution is.

;-)

Jeff
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
looking at it now mate
0
 

Author Comment

by:Stayer
Comment Utility
Hi and thank you for your reply.

I have never done any "Dim StrWhere as String" before, so I probably got it wrong:

This is what I did:
1: When into control button properties and pushed On Click

2: Then I inserted TextReports code, however something went wrong and I got an error message. The attachement provides the details.

Hopefully you can give me further instructions :)

PS! TheDate is replaced by Dato

rgds,

Stayer


screen2.bmp
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Its as I thought Jeff, if date is held in a date field and format is short date for example, it takes what is in the control panel international settings.
I assumed now when u created the date, you saw dates in US format.
I have opened it and see it in UK format.
I thought the calendar control returning a date would also work in the similar fashion.

I opened up the table to see dates in DD/MM/YYYY format
Ran the query, same thing
Ran the form, entered dates in DD/MM/YYYY format and and ran the report, got the expected results
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Stayer, extra bracket required to close cdate

strWHERE = "TheDate Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
If you are working with dates in MM/DD/YYYY, Im wondering why your calendar control is returning DD/MM/YYYY, unless of course you are not using the calendar control but some other popup.

Maybe its better to resolve the popup instead of fixing the return value. Reason why I say this is because you will run into the same problem again if you use it.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
rocki oops not enough )'s
The calendar control / built in calendar popup should be returning the date according to the Regional Settings.

CDATE will convert based on the regional settings then you need to format the date so it is in MM/DD/YYYY format, though you can also use YYYY-MM-DD in youyr SQL.

Cheers, Andrew
0
 

Author Comment

by:Stayer
Comment Utility
Hi and thank you for all the replies.

I am a beginner so I am a little confused what I should write, and where :)

Should I go into property and go On Click and write in the following:

strWHERE = "TheDate Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"

....and nothing more ? or do I need to add something extra coding to make it work.

rgds,

Stayer

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
you need to use the where in the docmd.openreport

Private Sub cmdViewReport_Click()
    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWhere
End Sub


I know your a newbie but always better to try resolve problems at source.

0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
You need to pur the code in http:#a24079239 but replace the strWHERE = line with the latest. Decided to do it below.
Cheers, Andrew
Dim strWHERE as String

strWHERE = "TheDate Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"

DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Andrew, if a calendary control is used then you shouldnt need to use CDATE surely as does it not return a date object.

But in this case, Im not sure a calendary control is being used due to mismatch in date format. So in this case, converting text to date, would require CDATE.

and in the code I forgot to add the strWhere, lol. Im sure Stayer picked up on that though.

Stayer, if you dont mind me asking, what type of calendar are you using?
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Hi rocki, looking at the original image it looks like it is typed in as it is left aligned.
Cheers, Andrew
0
 

Author Comment

by:Stayer
Comment Utility
Hi again; I really appreciate the efforts in helping me out here :)

I have now written the follwoing code:

Dim strWHERE As String
strWHERE = "Dato Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"
DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

Private Sub cmdViewReport_Click()
    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE
End Sub

As you can, see "TheDate" is replaced by "Dato" which is the field heading in the table the report is based on.

However, when I run it I get a new error message:

Compile error: Invalid outside procedure. What am I doing wrong. Please see attachment for the table and field I`m referring to and the codes.

How can I see what calendar i`m using. You can see the calendar symbol in the table - screenshot

rgds,

Stayer
screenshot-code.bmp
screenshot-table.bmp
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
All the code needs to go into your OnClick event from your command button, we have been assuming it is called cmdViewReport
Cheers, Andrew
Private Sub cmdViewReport_Click()

Dim strWHERE As String

    strWHERE = "Dato Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

End Sub

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Stayer, I said in my previous post, I forgot to add in your definition of strWHERE.
But Andrew has sorted that out.

Regarding calendar control, looks like access built popup. Im surprised then it is not using regional settings unless there is some settings somewhere.
0
 

Author Comment

by:Stayer
Comment Utility
Hi,

I copied and pasted TextReport last code, and when I click the command button I get a new syntax error:

As you can see from the attachement, I have inputtet start date: 06. of April 2009 and end date 07 of April 2009. This is written 06.04.2009 and 07.04.2009.
Syntax error message says: Between 04.06.2009#And#04.07.2009. It seems as if the / not working properly in the code.

How may I solve this_

rgds,

Stayer


screen4.bmp
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Ok, I checked and it seems to be using it correctly. The data you got shows DD.MM.YYYY so it must be in your regional settings.

What have you got in the regional settings ? (look in control panel)

Doing this will probably work also

Private Sub cmdViewReport_Click()
    DoCmd.OpenReport "rptKondisjon", acViewPreview, , "Replace(Dato,'.','/') Between " & "#" & Replace(Me.txtStart,".","/") & "#" & " AND " & "#" & Replace(Me.txtSlutt,".","/") & "#"
End Sub
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Can you please put a Debug.Print strWHERE in your code and tell us what is put into the immediate window.
Can you please confirm that the field DATO is a DateTime field.
Cheers, Andrew
Private Sub cmdViewReport_Click()

Dim strWHERE As String

    strWHERE = "Dato Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"

    Debug.Print Me.txtStart, Me.txtSlutt, strWHERE

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

End Sub

Open in new window

0
 

Author Comment

by:Stayer
Comment Utility
rockiroads, i tried toi integrate your last suggestion, but I got another syntax error
Private Sub cmdViewReport_Click()
Dim strWHERE As String
    strWHERE = "Dato Between " & "#" & Format(CDate(Replace(Me.txtStart, ".", "/")), "MM/DD/YYYY") & "#" & " AND " & "#" & Format(CDate(Replace(Me.txtSlutt, ".", "/")), "MM/DD/YYYY") & "#"
    DoCmd.OpenReport "rptKondisjon", acViewPreview, , "Replace(Dato,'.','/') Between " & "#" & Replace(Me.txtStart, ".", "/") & "#" & " AND " & "#" & Replace(Me.txtSlutt, ".", "/") & "#", strWHERE
     
End Sub

What is wrong

rgds,

Stayer

0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
in your code, u dont need to specify strWHERE in the last arg

just try this

Private Sub cmdViewReport_Click()
    DoCmd.OpenReport "rptKondisjon", acViewPreview, , "Replace(Dato,'.','/') Between " & "#" & Replace(Me.txtStart, ".", "/") & "#" & " AND " & "#" & Replace(Me.txtSlutt, ".", "/") & "#"
     
End Sub


0
 

Author Comment

by:Stayer
Comment Utility
Hi,

I am posting a sample of the database. Maybe it is easier this way to solve it.

Thanks for all the help guys

rgds,

Stayer
Sample-of-database2.mdb
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 300 total points
Comment Utility
also it seems it may be getting changed back to .

try this then, change format to YYYYMMDD


Private Sub cmdViewReport_Click()
    Dim sWhere As String
   
    sWhere = "Format(Dato,'YYYYMMDD') BETWEEN " & Format(Replace(Me.txtStart, ".", "/"), "YYYYMMDD") & " AND " & Format(Replace(Me.txtSlutt, ".", "/"), "YYYYMMDD")

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , sWhere
End Sub
0
 
LVL 28

Assisted Solution

by:TextReport
TextReport earned 200 total points
Comment Utility
I have just tested this in Access 2007 running Windows Vista Home edition and it worked first time with 2 records in the report.
The use of the variable is useful to troubleshoot problems like this and I would strongly recoment the use of it.
Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
The immediate window diwplayed the values:
06/04/2009    07/04/2009    Dato Between #04/06/2009# AND #04/07/2009#

Which is what I would have expected, therefore this does look like a Windows Control Panel, Regional Settings issue. in the meantime can you please try setting the format of the 2 controls to DD/MM/YYYY

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
I have just got the working code to fail by changing my regional setting to Estonia (Easter European), this has the short date format of DD.MM.YYYY. Once this was set I could then tackle the issue of the Code we have used previously and we have all missed that the CDATE() will convert to the regional settings. The code below converts the date correctly into the SQL String and the Debug.Print returns:

6.04.2009     7.04.2009     Dato Between #04/06/2009# AND #04/07/2009#

Cheers, Andrew

Private Sub cmdViewReport_Click()

Dim strWHERE As String

    strWHERE = "Dato Between " & "#" & Replace(Format(CDate(Me.txtStart), "MM/DD/YYYY"), ".", "/") & "#" & " AND " & "#" & Replace(Format(CDate(Me.txtSlutt), "MM/DD/YYYY"), ".", "/") & "#"

    Debug.Print Me.txtStart, Me.txtSlutt, strWHERE

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

End Sub

Open in new window

0
 

Author Closing Comment

by:Stayer
Comment Utility
Thank you so much for all your help on this one! By using :

Private Sub cmdViewReport_Click()
    Dim sWhere As String
   
    sWhere = "Format(Dato,'YYYYMMDD') BETWEEN " & Format(Replace(Me.txtStart, ".", "/"), "YYYYMMDD") & " AND " & Format(Replace(Me.txtSlutt, ".", "/"), "YYYYMMDD")

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , sWhere
End Sub

....it now works perfectly :)
rgds,

Stayer
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Cool. It was obvious it was a regional settings u was using when u had . appearing, was just a case of figuring how to tackle it. Glad to see your up and running now.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Just a final note that the use of the format on the field will have a detrimental affect of the performance of the query, while this may not be noticable due to the size of the database it is worth remembering for the future.

Finally the use of the ISO date format of YYYY-MM-DD will work for you, from my point of view I think I should try to remember to use this rather than MM/DD/YYYY that I have been using for the last 17 years in Access. This is optermised and is not affected by the regional settings.

The Debug.Print returns:
6.04.2009     7.04.2009     Dato Between #2009-04-06# AND #2009-04-07#

Cheers, Andrew
Private Sub cmdViewReport_Click()

Dim strWHERE As String

    strWHERE = "Dato Between " & "#" & Format(Me.txtStart, "YYYY-MM-DD") & "# AND #" & Format(Me.txtSlutt, "YYYY-MM-DD") & "#"

    Debug.Print Me.txtStart, Me.txtSlutt, strWHERE

    DoCmd.OpenReport "rptKondisjon", acViewPreview, , strWHERE

End Sub

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
KB'd!

Thanks Andrew and rocki.

Where would little JeffCoachman be without you.
;-)
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Jeff, nothing wrong with your solution in the first place. Regional settings didnt take into account then.
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
Rocki absolutely, this got me thinking though so I consulted the Access 2007 help on the Format Function

(:) Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system settings.

(/) Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your system settings.

I have to say "Well I didn't know that", but there was no mention of the hyphen (-)

Cheers, Andrew
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
Andrew, I knew about your regional settings but I had thought you could override this if you use a specific mask instead of using Short Date. The latter is what is defined in the settings.

Thats one reason why I used the mask YYYYMMDD, I converted it to a number and that mask ensures it is in numerical order. Idea was to not compare as dates. When you said to use YYYY-MM-DD, I wasnt 100% sure if that would work, as I thought that might be treated as a date again.
0
 
LVL 65

Expert Comment

by:rockiroads
Comment Utility
ooops - about "the" regional settings, not "your" regional settings
0
 
LVL 28

Expert Comment

by:TextReport
Comment Utility
From what I have learnt this year while being back actively at EE is that MS have allowed the use of the ISO standard date format YYYY-MM-DD in SQL for quite some time but I was (still am) so intrechched in usign MM/DD/YYYY that I keep forgetting it. My main point above is that the / is regionalised and I certainly didn't realise that.

So from now on it is YYYY-MM-DD all the way, well until I forgot and habit forces me back to MM/DD/YYYY

"Thats one reason why I used the mask YYYYMMDD" as you said it got it working, as I have said there could be an issue with performace on very large tables but first thing is to get a working solution. As stated I did not know about the / in the date format until after / around when you answer was accepted so it really is a learning point for everyone, a complete change in something I have been doing for 17 years.

Best wishes Andrew
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now