Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Syntax/Logic Error (XP)

    I am having a heck of time getting a query to give me what I want, instead of what I ask for.

    I have a form with a combo box and a text box. The combo box allows selecting a month (01-12), and the text box allows me to type in the year. When I select a month and year using the form, I need to query the table(s) on previous month's results. (For example, if I select "02" from the combo box, and type in "2004", I want the query to give the results for Jan 2004). The following SQL works fine EXCEPT when the month is January ("01").

    SELECT FMil_intEnd_of_Month_Mileage, F_intCorp_No AS CorpNum, Year([FMil_datMileageDate]) & Format(Month([FMil_datMileageDate]),"00") AS MonthYear
    FROM tblFleet INNER JOIN tblFleetMileage ON tblFleet.f_PK_ID = tblFleetMileage.FMil_Fkey
    WHERE (((Year([FMil_datMileageDate]) & Format(Month([FMil_datMileageDate]),"00"))=IIf([Forms]![frmMileageAdd]![cboMonth]="01",([Forms]![frmMileageAdd]![txtYear]-1) & "12",[Forms]![frmMileageAdd]![txtYear] & Format([Forms]![frmMileageAdd]![cboMonth]-1,"00"))));

    This is so simple, I am embarrassed to ask, but I just cannot find the solution. Any help is greatly appreciated.

    K

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Syntax/Logic Error (XP)

    Hard to say without seeing the actual form, but try replacing

    IIf([Forms]![frmMileageAdd]![cboMonth]="01", ...

    by

    IIf([Forms]![frmMileageAdd]![cboMonth]=1, ...

    or by

    IIf(Format([Forms]![frmMileageAdd]![cboMonth],"00")="01", ...

  3. #3
    4 Star Lounger
    Join Date
    Jan 2003
    Location
    Central Florida, USA
    Posts
    505
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: Syntax/Logic Error (XP)

    Hans,

    THANKS! That was it, I couldn't see the "formats" for the trees, I mean the "forms". LOL
    IIf(Format([Forms]![frmMileageAdd]![cboMonth],"00")="01", ... was the solution


    BTW - I want to thank you for introducing me to the Dsum, DCount, DAverage functions with the correct formatting. They have come in useful several times in the last several weeks, and I have meant to post a thank you specifically for that. Thanks!

    Ken

Posting Permissions

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