typemismatch error in vba code

[swarv]

Hi all,

I have the following code:

LTotal3 = DCount("[Name]", "absent", "[Name] = " & Chr(34) & Combo136.Value & Chr(34) & " And [absentcode]='countme'" And [Start_Date] > ((DateAdd("m", -12, Date))))
LTotal4 = DSum("[Days]", "absent", "[Name] = " & Chr(34) & Combo136.Value & Chr(34) & " And [absentcode]='countme'" And ((DateAdd("m", -12, Date)) < [Start_Date]))

In the first line it comes back with type mismatch error.
Is there a quick reason for this?

Notes:
absent is a table name
Start_Date is a field name

I think the error is with the bit that goes:
And [Start_Date] > ((DateAdd("m", -12, Date)))

Cheers

 

[gemma-the-husky]

this is the problem

And [Start_Date] > ((DateAdd("m", -12, Date))))

with dates the string needs to look like this, when its finished

[start_date] > #12/2/09#

so

"And [Start_Date] > #" & ((DateAdd("m", -12, Date))) & #"

so you need to structure the statement to include the # characters.

note also that the date in this case will ALWAYS be treated as a US date (ie Dec 2nd), so if you are in UK you need to add further formatting instructions to prevent this (although I am not 100% sure with the way you are manipulating the date, as it stands)

 

[swarv]

hi gemma,


thanks for your answer,

I now have the following:

LTotal6 = DSum("[Days]", "absent", "[Name] = " & Chr(34) & Combo136.Value & Chr(34) & " And [absentcode]='countme'" And [Start_Date] > "# & ((DateAdd("m", -12, Date))) & #"

it is erroring on the m just after the DataAdd.
I have changed the " and #'s around.

Any ideas?

Thanks again

 

[swarv]

I have even tried this:

LTotal6 = DSum("[Days]", "absent", "[Name] = " & Chr(34) & Combo136.Value & Chr(34) & " And [absentcode]='countme'" And "Start_Date >#" & DateAdd("m", -12, Date) & "#")

cheers

 

[swarv]

I got round the DCount line by running this is a query and then calling it with the Dcount function

SELECT *
FROM absent
WHERE (DateAdd("m",-12,Date())) <  (((absent.Start_Date)));

But I cant do that with the DSum function so any ideas are welcome as im getting stuck


cheers

 

[swarv]

can somebody help or can i use dsum in the way as i did that query above? cheers