Data mismatch help

[JBurlison]

Getting a data mismatch error here:

DoCmd.OpenForm "Reason For User Change", , , "[Reason For User Change]![Sai Serial] = " & Me.Sai_Serial_Number And "[Reason For User Change]![Username] ='" & Forms![InfoKeeper]![User Asigned] & "'" And "[Reason For User Change]![Date Changed] = " & Userdate

Sai Serial is a number

Username is string

Userdate is set as string but its a date

am i formatting my where condition wrong?

 

[doco]

What is the purpose of the word 'And' inserted in the string?

 

[georgedwilkinson]

Change:
"'" And "[Reason For User Change]
To:
"' And [Reason For User Change]!

 

[JBurlison]

And will apply multiple conditions to the WHERE filter right?

 

[georgedwilkinson]

Exactly. But the extra 2 "s make it do something else (i.e. give an error because you're doing a boolean AND operation where it doesn't belong).

 

[georgedwilkinson]

Also, I just noticed your date at the end. That should also be delimited, I think.

Change:
" & Userdate

To:
#" & Userdate & "#"

 

[JBurlison]

Same thing im a lottle confused at this point

If DCount("[Sai Serial]", "Reason For User Change", "[Reason For User Change]![Sai Serial] = " & Me.Sai_Serial_Number) > 0 And DCount("[Date Changed]", "Reason For User Change", "[Reason For User Change]![Date Changed] = '" & Userdate & "'") > 0 And DCount("[Username]", "Reason For User Change", "[Reason For User Change]![Username] ='" & Forms![InfoKeeper]![User Asigned] & "'") > 0 Then

 

[georgedwilkinson]

Lots of syntax errors. Extra parentheses, incorrect delimiters on date fields, misplaced double quotes, etc. Break it down into pieces and try again.

 

[JBurlison]

Got that one working having trouble with this still tho??

DoCmd.OpenForm "Reason For User Change", , , "[Reason For User Change]![Sai Serial] ='" & Me.Sai_Serial_Number & "'" And "[Reason For User Change]![Username] =" ' & Forms![InfoKeeper]![User Asigned] & "'" And "[Reason For User Change]![Date Changed] ='" & Userdate & "'"

 

[georgedwilkinson]

I answered that one earlier. It has " around the AND and doesn't have # around the date.

 

[JBurlison]

all the filters work indvidually but when i put the "And" in it dose not work how do i apply multiple filters to this command?

 

[pbaldy]

As George has answered, the And is not placed correctly. Basically you only want your form references outside the quotes. Everything else inside, including the And.

 

[JBurlison]

im not sure what you mean here here is all 3 of the arguments i wish to include:

"[Reason For User Change]![Date Changed] =#" & Userdate & "#"
"[Reason For User Change]![Sai Serial] ='" & Me.Sai_Serial_Number & "'"
"[Reason For User Change]![Username] ='" & username & "'"

DoCmd.OpenForm "Reason For User Change"

could you give me an example how to properly apply multiple filters at a time im not sure how to do it.

 

[pbaldy]

In your existing effort you basically have this:

"[Reason For User Change]![Date Changed] =#" & Userdate & "#" And

and it needs to be this:

"[Reason For User Change]![Date Changed] =#" & Userdate & "# And "

as George pointed out in post 3.

 

[JBurlison]

Its giving me expected end of statment.

DoCmd.OpenForm "Reason For User Change", , , "[Reason For User Change]![Username] ='" & username & "' And"[Reason For User Change]![Sai Serial] = '" & Me.Sai_Serial_Number & "'And "[Reason For User Change]![Date Changed] =#" & Userdate & "#"

what am i doing wrong here for some reason i just dont get it someones going to have to hit me upside the head with the answer on this one

 

[pbaldy]

DoCmd.OpenForm "Reason For User Change", , , "[Reason For User Change]![Username] ='" & username & "' And [Reason For User Change]![Sai Serial] = '" & Me.Sai_Serial_Number & "' And [Reason For User Change]![Date Changed] =#" & Userdate & "#"

 

[JBurlison]

wow it jut hit me too i had an extra " in the mix after each "And". ugh im so thick sometimes

 

[georgedwilkinson]

Not sure if you fixed this yet. Your last query has an extra double quote mark next to the AND.