Hi guys Please help me with this query:)
Whenever i run this query it gives an error>>(Syntax Error converting Datetime from character String).

SELECT dbo.Patient_Info.Kpaids_num, dbo.Patient_Info.Docket_num, dbo.Patient_Info.First_Name, dbo.Patient_Info.Last_Name,
                      dbo.Patient_Info.Home_Number, dbo.Scheduled_Appointment.App_Date, dbo.Scheduled_Appointment.App_Time, dbo.Scheduled_Appointment.clinic,
                      dbo.Scheduled_Appointment.Visit
FROM dbo.Patient_Info RIGHT OUTER JOIN
                      dbo.Scheduled_Appointment ON dbo.Patient_Info.Kpaids_num = dbo.Scheduled_Appointment.Kpaids_num
WHERE (dbo.Scheduled_Appointment.App_Date = CONVERT(datetime, '[forms]![Appointments_View].[Text1]', 101))

Ah, you have a SQL back end, and are referring to an Access date format.

What I have done is take the Access date first, and convert it to a SQL date format using regular expressions. Then you can use it to parameterize the query.

Example:

Dim sMyDate As String
Dim mm As String
Dim dd As String
Dim yyyy As String

sMyDate = Me.Text1
mm = Left(sMyDate, 2)
dd = Right(Left(sMyDate, 5), 2)
yyyy = Right(sMyDate, 4)

sMyDate = yyyy & "-" & mm & "-" & dd

...
WHERE(dbo.Scheduled_Appointment.App_Date = sMyDate)

Does this work? I don't know what your SQL date format is.





mmcdonal

Alternatively, you can do this:

DateArray = Split(sMyDate, "/")

Then get your month, day and year values, check for length, add zeros, etc.

HTH

mmcdonal

The alternative using Split(sMyDate, "/") is the one to use. The first method will fail if a date is entered without leading zeros, such as "6/5/06".

On the other hand. You could take the text string containing the date. Convert it to a date. Then use the Month(), Day() and Year() or DatePart() functions to extract the various parts before building the SQL Server query.

Rand

Yeah, I thought of the leading zeros issue and made the second post. In the database I am running with this function, the dates are all in the format yyyy-mm-dd and always with leading zeros, so the first method works fine.



mmcdonal

We use both Access and SQL Server in house. I've had to deal with this problem too many times. It would be handy if Access had an option to use SQL Server wild cards and formatting instead of the old DOS wild card characters.

Rand

ok guys thx for the responses, i'm gonna try these new options.

I'm actually looking for a solution tht works with a Query in the Back-end, this query function is to generate a list based on a Date value entered in the form. so in essence i want to create a query with the WHERE CONDiTION reading a date value from an input box "Text1".

the data type used for the Date is "Datetime".