stLinkCriteria with date field

[bearatc]

I'm getting a syntax error with the following string:

stLinkCriteria = "[Injury_No]=" & Me![Injury_No] & "'And[RehabDate]=#" & Me![RehabDate] & "#"

Can anybody tell me where I messed up? The field "Injury_No" is a number and "RehabDate" is a date field. I know with a date field the syntax is kind of screwy.

 

[vbaInet]

I've highlighted all the areas that were fixed:

stLinkCriteria = "[Injury_No][COLOR=Red] = '[/COLOR]" & Me![Injury_No] & "[COLOR=Red]' A[/COLOR]n[COLOR=Red]d [[/COLOR]RehabDate] = #" & Me![RehabDate] & "#"

 

[bearatc]

Thanks vbaInet. I copied and pasted exactly what you have and now it is giving me the "Data type mismatch in criteria expression"

 

[vbaInet]

Is the error stopping at that line?
What did you declare stLinkCriteria as?

 

[bearatc]

No, the error occurs when I click the button to try to run the report. The report is based on a query, so the criteria is to limit the report. Here is the entire code for the button:

Private Sub RehabReportBut_Click()
On Error GoTo Err_RehabReportBut_Click

Dim stDocName As String
Dim stLinkCriteria As String



stLinkCriteria = "[Injury_No]=" & Me![Injury_No] '& "'And[RehabDate]=#" & Me![RehabDate] & "#"





stDocName = "RehabNotes2"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

Exit_RehabReportBut_Click:
Exit Sub

Err_RehabReportBut_Click:
MsgBox Err.Description
Resume Exit_RehabReportBut_Click

End Sub

 

[vbaInet]

You didn't copy and paste what I gave you.

 

[bearatc]

Sorry, there is not a " ' " before the &. I just did that to make sure the first part of the code worked. And it did

 

[vbaInet]

That was not the only thing I changed. I added some spaces in relevant places too but your code doesn't have any of those spaces.

 

[bearatc]

You are right, the code that is in there now was not what you sent, but I just copied and pasted what you had sent earlier, but I had to change it back to get the first part to work. When I used your code for the stLinkcriteria, is when I got the data type mismatch

 

[bearatc]

The spaces were automatically placed in the code when I copied and pasted, but when I copy and paste onto this forum, it doesn't reflect the spaces. I will try again

 

[vbaInet]

If you copy the old code, it's like going backwards.

So, comment out the on error line and run the code again. You know how to apply a comment right? When you run the code it will bring up a message box, click on Debug and that will take you to the line where the error is happening.

 

[bearatc]

I think I know what you are talking about. but when I run the form and click the button to open the report, it just gives me an error box and no choice to debug. When I'm in the Visual Basic box, and I try to "run" it, it doesn't do anything.

 

[bearatc]

I got it now. I removed the " ' " in front of the And.

Here is the code that works now:

stLinkCriteria = "[Injury_No]=" & Me![Injury_No] & "And [RehabDate]=#" & Me![RehabDate] & "#"

Thank you for your help, anyway.

 

[vbaInet]

Did you comment out the On Error line like I mentioned in my last post?

 

[bearatc]

No, I didn't, I just tried removing the single '. But I now know what you are talking about and I'm sure it will help me in the future. Thanks again.

 

[vbaInet]

Good to see you worked it out.

But you still need a space before the AND (if you don't already have it).