Does Month() convert to character? (2 Viewers)

[CrystalSurfer]

Hi all,
I have created a form that lists work to be invoiced.
However I have a problem with the following WHERE clause in the query source:

WHERE (
  IIF(
    A.DateComplete is not null,
    Month(A.DateComplete),
        IIF(A.Fee is not null,
          IIF(F.DateSpecific=False,
          1,
          13),
        13,
      )
  )
  <=([Forms]![frmInvoiceRun01].[cboMonth])
)

I only want to show lines that have a month less than or equal to the selected month on the form.
Unless I've got this all wrong (which I have otherwise I wouldnt be here!) it appears to convert the first part of the check into a character value because when I select September (month 09) it shows more lines than if I select October (10).
This means that the calculated months of 10, 11 and 12 are shown because they are less than "9" but not "10".

For info, the month calc uses fields from tables A and F. It is also returning a value of 13 in those conditions that need to be filtered out.

Please can someone put me out of my misery.
Thanks.

 

[neileg]

You IIF statement should return a number. If you want to be 100% sure wrap it in a CInt() function.

Is your combo bound column a number? Again you could use a CInt() to force this.

 

[DCrake]

You could try wrapping Val() around your Month() statement to test

 

[CrystalSurfer]

It must be Monday morning-itus. I've been testing the CInt() but couldnt get it working until now!

Thanks for your help gents.

 

[CrystalSurfer]

Oh dear. I spoke too soon.
I copied the query into the form source and got a "This expression is typed incorrectly, or it is too complex to be evaluated.." when firing it up.
It could be related to the bound selection column which is a simple query and definitely a number.
I tried putting the CInt() around the Month in that combo source query rather than in this query but still no joy.

 

[neileg]

Which is the bound column in the combo? It usually works best if the bound column is first. If your month number is not the bound column, you will need to reference the column number. Remember that column numbers are zero based so the first column is column 0, the second is column 1, etc.

 

[namliam]

Why use an IIF at all in the where?? Just do ANDs and ORs... thats what the where is for

Your IIFs should roughly translate into something like...

WHERE  Month(A.DateComplete) <= [Forms]![frmInvoiceRun01].[cboMonth]
or F.DateSpecific= False

This will work as you want it too assuming your cboMonth can contain values 1 thru 12, hence you cannot select anything that is "DateSpecific = True AND DateComplete is null"

Also keep in mind dates/months have problems attached to it concerning years... You may need to address that as Month(dateComplete) can return 12. If I select "1" in your combobox I will want to invoice 12 as well, but that will not happen in this instance because 12 is never less than 1.

And YES, Month() returns a number

 

[CrystalSurfer]

The combo is a simple ID, MonthName query over tblMonth with the bound column as ID which is doubling up as the month number 1 to 12. Set as an long int, autonumber. So no problem there I think(!)

I am beginning to suspect its my complex IIF statement so will try pulling out it out into separate lines.
Thanks for your ideas, I really appreciate this.

 

[namliam]

Just use the OR like I gave you the sample for... There is NEVER a need to use an IIF in the Where clause...

In the select, yes... Group by, yes ... anywhere else... no...

 

[CrystalSurfer]

Point taken Mailman. I'll always try to make things more complicated if given half a chance!

OK, I've done as you suggested and simplified the criteria as follows:

WHERE 
(
  ((C.YearCon) Like fcboSearch([Forms]![frmInvoiceRun01].[cboYear])) 
AND 
  ((IsNull([A].[DateInvoiced]))<>False) 
AND 
  (
    (Month([A].[DateComplete])<=([Forms]![frmInvoiceRun01].[cboMonth]))
    OR
    (F.DateSpecific=False)
  )
)

Unfortunately I'm still getting the same result.
ie: Month([A].[DateComplete]) value of 12 is being selected when ([Forms]![frmInvoiceRun01].[cboMonth]) value = 9.

When using CInt() around the cboMonth I get the 'too complex to be evaluated' error. But I shouldnt need to do this because the bound column is an integer anyway.

I've used CInt(Nz(A.DateComplete,13)) to filter out those null dates.
In testing this query both columns appear to be numeric (right justified). If they were left justified I'd understand it.
But I really don't understand it! Im rapidly losing the will to live here..

 

[namliam]

OK 'proper' SQL ...
IsNull([A].[DateInvoiced])<>False)

Should be
[A].[DateInvoiced] is null

I.e. you dont want it to be invoiced


Now lets start taking out the overdone () that Access always does, still looks OK.
?month(#31-12-2008#) < 9
False
Works perfectly!

The only thing I can think of is that the DateSpecific is actually "False" for these records that are mont() 12.

Cou8ld you post (this part) of your DB? Maybe that will provide more clues... Month though absolutely returns a int, however if your form control is beeing used as a string (for some reason) Access will convert the month to a string as well that may explain your behaviour
?month(#31-12-2008#) < "9"
True

Try putting Value(Forms]![frmInvoiceRun01].[cboMonth]), Neileg said something simular earlier.

 

[neileg]

Try putting Value(Forms]![frmInvoiceRun01].[cboMonth]), Neileg said something simular earlier.

I suggested CInt([Forms]![frmInvoiceRun01].[cboMonth]), but either should work.

 

[namliam]

Cint, Value

Patato Potato...

 

[CrystalSurfer]

The CInt([Forms]![frmInvoiceRun01].[cboMonth]) (and Val) gives me the 'too complex to evaluate' error. Although it runs perfectly well as a standalone query.(?)

Even stripping down the selection criteria to just (Month([A].[DateComplete])<=([Forms]![frmInvoiceRun01].[cboMonth])) doesnt get it working, so it isn't doing anything odd with the other checks.
I'll see if I can post this part of the db..
Thanks

 

[namliam]

Sorry, but then without the database I am fresh out of ideas