Results 1 to 7 of 7
  1. #1
    ittiekat is offline Novice
    Windows 8 Access 2013
    Join Date
    Feb 2016
    Posts
    5

    Question Syntax error in query expression

    I have the below formula in one of my queries:



    IIf([BeginDate]<#1/1/2009# And [EndDate]>#1/1/2010#,365,IIf([BeginDate]<#1/1/2009# And [EndDate]<=#1/1/2010# And [EndDate]>#1/1/2009#,DateDiff("d",#1/1/2009#,[EndDate]),IIf([BeginDate]>=#1/1/2009# And [BeginDate]<#1/1/2010# And [EndDate]>=#1/1/2010#,DateDiff("d",[BeginDate],#1/1/2010#),IIf([BeginDate]>=#1/1/2009# And [BeginDate]<#1/1/2010# And [EndDate]<#1/1/2010#,DateDiff("d",[BeginDate],[EndDate]),0))))

    The formula appears to be working fine and I'm getting the values I expect. The problem is that the field is being detected as text format and when I try to filter in any way, I get "Syntax error (missing operator) in query expression".

    I'm well aware of how I could force this value to be a number, however, I'd like to just correct the syntax error so that it is correct. Can anyone tell what my syntax error is? Unfortunately I can't find, and it surprises me that the expression still calculates correctly. This expression is in a new column of the query, so its not like it is being forced into a previously formatted field.

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Sounds like you have values in your date fields that are not dates. are the date fields truly date fields or are they text?

  3. #3
    ittiekat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    5
    I checked my source table and the [BeginDate] and [EndDate] fields are definitely formatted to Date/Time. I would think that if the fields were not date fields then the expression would not even work. It does work, though, and I've spot checked that its calculating correctly. The error only comes around when I try to apply any type of filter to the column.

  4. #4
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Can you export the returned data from the query to excel and filter it there?

    So you want the values to be returned as a whole number? Try CInt()
    like
    Cint(IIf([BeginDate]<#1/1/2009# And [EndDate]>#1/1/2010#,365,IIf([BeginDate]<#1/1/2009# And [EndDate]<=#1/1/2010# And [EndDate]>#1/1/2009#,DateDiff("d",#1/1/2009#,[EndDate]),IIf([BeginDate]>=#1/1/2009# And [BeginDate]<#1/1/2010# And [EndDate]>=#1/1/2010#,DateDiff("d",[BeginDate],#1/1/2010#),IIf([BeginDate]>=#1/1/2009# And [BeginDate]<#1/1/2010# And [EndDate]<#1/1/2010#,DateDiff("d",[BeginDate],[EndDate]),0)))))


  5. #5
    ittiekat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    5
    Thank! I know how to force it to be a whole number. I'm more interested in understanding what is causing the syntax error, and also what is causing the field to be in text format.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was looking at your expression and can't see anything wrong. I was confused a little with the dates so I rewrote it a little. I added parentheses to ensure groupings and used the Between keyword.
    Code:
    IIf(([BeginDate]<#1/1/2009#) And ([EndDate]>#1/1/2010#),365, IIf(([BeginDate]<#1/1/2009#) And ([EndDate] Between (#1/1/2009# + 1) And #1/1/2010#), DateDiff("d",#1/1/2009#,[EndDate]), IIf(([BeginDate] Between #1/1/2009# And (#1/1/2010# - 1)) And ([EndDate]>=#1/1/2010#),DateDiff("d",[BeginDate],#1/1/2010#), IIf(([BeginDate] Between #1/1/2009# And (#1/1/2010# - 1 )) And ([EndDate]<#1/1/2010#),DateDiff("d",[BeginDate],[EndDate]), 0))))
    Do the numbers match your original expression?


    Have you tried just
    Code:
    IIf(([BeginDate]<#1/1/2009#) And ([EndDate]>#1/1/2010#),365, 0)
    and filter on the numbers?
    If that works, keep adding one IIF() statement until you get the error.

  7. #7
    ittiekat is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Feb 2016
    Posts
    5
    That's an idea. I might try one IIf() statement at a time and see when the error kicks on.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-10-2014, 11:30 AM
  2. Replies: 11
    Last Post: 05-01-2014, 11:56 AM
  3. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  4. Syntax error in query expression
    By That_Guy in forum Programming
    Replies: 3
    Last Post: 08-17-2012, 09:01 AM
  5. Replies: 6
    Last Post: 05-30-2012, 12:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums