Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2013
    Posts
    15

    Unanswered: Crosstab Query Error No. 3070 Because Of Date Field(s), Access 2007

    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.

    Click image for larger version. 

Name:	crosstab_issue_date.jpg 
Views:	5 
Size:	119.5 KB 
ID:	16154

    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!

    Cheers,
    Adi

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is no interval between the same dates
    so id suggest instead of between use
    Greater than or equal to start and less than ir equal to end date.

    One 'gotcha' to bear in mind is if your datetime value includes a time element. If so then you need to realuse that not specifying the time means midnight
    so 04 feb 2015 means 04 feb 2015 00:00:00 ie the start of 4th feb, nor the end of that day. Nit a problem for a start date but will be a problem for an end date IF you have a time component in the datetime value.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Another possibility:

    http://support.microsoft.com/default...b;en-us;209778

    In my experience, Between works fine with two dates that are the same (presuming no time element)
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •