[RESOLVED] SQL Searches with multiple criteria

[WestFiasco]

Hello, i'm new here and looking for assistance in completing a school project.

This is what i have

adoAppointments.RecordSource = "Select Appointments, Time from Appointments where ID = " & "'" & adoPatientRecords.Recordset!ID & "'" And Appointments] = " & " '" & Date & "'"

adoAppointments.Refresh

I would like to be able to search for any Appointments with multiple criteria (the ID & Date), what i have doesn't seem to be the correct syntax. Any help i receive would be greatly appreciate, Thanks in advance.

[DataMiser]

You are on the right track, If ID is a numeric field then you should not be using the ' around it as that implies a non numeric field
Date fields do use ' if you are using SQL server db if using Access then you would use a # instead of the ' as the delimiter around the value
is the ] actually part of the field name? if not then this would be a problem.

[WestFiasco]

Hi, Thanks for the Quick reply

the ] was an accident which i have since removed, The ID is a text field and i am using access. Did you mean it should be like this?

adoAppointments.RecordSource = "Select Appointments, Time from Appointments where ID = " & "'" & adoPatientRecords.Recordset!ID & "'" And Appointments = " & "#" & Date & "#"

That line gives me a syntax error, the first part of the code works it's from *And* where it all goes wrong. I've been tweaking it all morning to no avail.

[jggtz]

adoAppointments.RecordSource = "SELECT Appointments, Time FROM Appointments WHERE ID = '" & adoPatientRecords.Recordset!ID & "' AND Appointments = #" & Date & "#"

Also I would use a formated date

[WestFiasco]

Thanks A lot, that got it to work as it should. Have a great day.