Report error when date range is Dec 2010 only

[Jon123]

I open a report using a date range and everything looks good. But if I do a date range for the month of Dec 2010 I get this error I don't understand why??? I looked up the help notes and it is saying I have typed the expression wrong. OK but why does it work if I do a report for Jan of 2010 ?

jon

 

[vbaInet]

If we were psychic we would have been able to figure out what you typed incorrectly. But since we sadly don't have these abilities, perhaps you could paste the code and the sql here.

 

[Jon123]

Thats what I don't get either. If I select the debug command on that error box this is what shows the problem to be. and again I only get the error when my report date range is 12/1/2010 - 12/31/2010 if its 1/1/2011 - 1/31/2011 all works fine infact any month range in 2010 works execpt dec

DoCmd.OpenReport stDocName, acPreview

I dont get it

jon

 

[vbaInet]

I still need to see the sql statement of your report's record source Jon.

Are you feeding these dates off a textbox or manually typing in the criteria?

 

[Jon123]

ok sorry

SELECT [Daily].[customer ID], [Daily].[Date of Visit], [Tble-People].[Last Name], [Tble-People].[First Name], [Daily].[Numberclient], [Tble-People].[Number of Adults], [Tble-People].[Number of Children]
FROM [Tble-People] LEFT JOIN [Daily] ON [Tble-People].ID = [Daily].[customer ID]
WHERE ((([Daily].[Date of Visit]) Between [Forms]![Frm-reports main]![Date1] And [Forms]![Frm-reports main]![date2]))
ORDER BY [Daily].[Date of Visit];

 

[vbaInet]

Ok, it seems fine.

Do these things:

1. Open the query in design view and look for a menu called Parameters and set it like this:

Parameter                                         Data Type
[Forms]![Frm-reports main]![Date1]       Date/Time
[Forms]![Frm-reports main]![date2]       Date/Time

2. Set the Format property of both text boxes to Short Date

 

[boblarson]

Jon -

Since you are in the U.K. try using this instead in your parameter:

Between Format([Forms]![Frm-reports main]![Date1], "#mm\/dd\/yyyy#") And Format([Forms]![Frm-reports main]![date2], "#mm\/dd\/yyyy#")))

 

[Jon123]

tried and still getting the same error. (12/1/2010 - 12/31/2010) fails but
(12/1/2011 - 12/31/2011) works fine

 

[vbaInet]

What is the data type of [Date of visit]?

 

[Jon123]

Date/Time format

 

[vbaInet]

Did you try Bob's suggestion?

Maybe we can see your db?