Crosstab query Error no. 3070 because of date field(s)

[adi2011]

Hi,

I have simple crosstab query that I use to pull data about how many products is selled in one day/week/month and so on. On entry form I have two critical date fields, Starting/Ending date. It all works perfect except when I put indetical values in both those fields.

For example I have sold over 100 items on 4th of february 2015. year. So, in Starting/Ending date field of entry form, I put value from 04/02/2015 to 04/02/2015 in order to see how much items was selled that day. Please see image attached which explains the situation.

This is the error I receive:

An error was encountered

Description: The Microsoft Office Access database engine does not recognize '' as a valid field name or expression.

Error Number: 3070

What am I doing wrong?

Thanks in advance for prompt replies!

Ceers,
Adi

 

[boerbende]

Suggestion:
Could it be that with identical fields, you don't find any records (there is nothing in between 1 and 1 either), so Access finds no records, no headers and a crosstab cannot have empty headers

 

[adi2011]

Hi Ben,

I don't understand, I really have many records on that date.. It is not Null for that particular date, I am 100% sure.

BR,
Adi

 

[jdraw]

adi,

Where are you located, your date format may be the issue?
see http://allenbrowne.com/ser-36.html

 

[boerbende]

I see it.
When you start "query design" and create an identical query but type in the dates, does this give correct results and headers?
Then you see also the date format as it should be like jdraw is referring to

 

[Misiek]

I had a similar issue a while ago and found the use of word "between" was causing issues, therefore try this:

>=[Forms]![FormName]![StartDate] And <=[Forms]![FormName]![EndDate]