Data type mismatch in criteria expression

W

Wind54Surfer

Hi again,

I am starting a new thread because the query turned out to be wrong.

Now i am getting the "data mismatch" error on the following part:
-------------------------------------------------------------------
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=""" & _
Schedule_ID & """") & "', "
-------------------------------------------------------------------

Tha complete query is:
------------------------------------------------------------------
Function build_sql(Schedule_ID As Date, operation As String) As String
build_sql = "Insert Into tblJobScheduleAuditLog Values ("
build_sql = build_sql & ScheduleID & ", "
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=""" & _
Schedule_ID & """") & "', "
build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=""" & _
Schedule_ID & """") & "', "
build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=""" & _
Schedule_ID & """") & "', "
build_sql = build_sql & "'" & operation & "', "
build_sql = build_sql & "#" & Now() & "#)"
End Function
----------------------------------------------------------------------
In the table "tblJobScheduleAuditLog"

ScheduleID is Date/Time
Scheduled is Text
JobDate is Date/Time
Status is Text

I tried many things but I am stuck.

Thanks for any help,
Emilio
 
J

John Vinson

Hi again,

I am starting a new thread because the query turned out to be wrong.

Now i am getting the "data mismatch" error on the following part:
-------------------------------------------------------------------
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=""" & _
Schedule_ID & """") & "', "
-------------------------------------------------------------------
Click to expand...

This would be appropriate if the datatype of ScheduleID were Text. If
it is Number, you'll get this erro.r. Try losing the quotes:

DLookup("JobDate", "JobSchedule", "ScheduleID=" & _
Schedule_ID) & "', "'


John W. Vinson[MVP]
 
D

Duane Hookom

That's I believe what he started with in the previous thread and had the
same error. That's why I suggested he add the quotes. He still hasn't told
us what the actual data type is.
 
B

Brian Bastl

Hi Guys,

Just wondering how this will work in any case without specifying the fields
in the receiving table.
build_sql = "Insert Into tblJobScheduleAuditLog (<missing field names>)
Values ("

Just curious.
Brian
 
W

Wind54Surfer

Thanks John,

I did the changes but now I get:

Syntax error (missing operator) in query expression ‘ScheduleID= 1/8/2006
7:54:33 PM’

The error happens in:
 
J

John Vinson

Thanks John,

I did the changes but now I get:

Syntax error (missing operator) in query expression ‘ScheduleID= 1/8/2006
7:54:33 PM’

The error happens in:
Click to expand...

Your ID is a DATE/TIME field!!!?

You have been asked several times for the datatype of this field. You
have chosen not to reply to this ESSENTIAL question.

If ScheduleID is in fact of Date type it must be delimited by #:

DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "

John W. Vinson[MVP]
 
W

Wind54Surfer

Thanks again John,

For the record when I started this thread I wrote:
-----------------------------------------------------------------
ScheduleID is Date/Time
Scheduled is Text
JobDate is Date/Time
Status is Text
-----------------------------------------------------------------
I thought that would explain what the fields were.

Also on my other thread I was not using "ScheduleID" (Date/Time) but
"Schedule" (text) that is why I didn't feel I had to explain it since Duane
told me what I should do.

I am stil having problems, now the eror is
------------------------------------------------------------------------------
Syntax error (missing operator) in query expression ‘1/9/2006 12:19:23 AM’
-----------------------------------------------------------------------------
now with the breakpoint I found stops at: "conn.Execute ssql" at Sub below
--------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_end
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
If NewRecord = False Then
ssql = build_sql(ScheduleID, "Update")
conn.Execute ssql
conn.Close
Set conn = Nothing
Else
If IsNull(Scheduled) Or Scheduled = "" Then
MsgBox "Must Enter Schedule"
Cancel = True
End If
End If
Exit Sub
err_end:
MsgBox Err.Description
End Sub
------------------------------------------------------------------------

I am trying to apply something I took from a book that I bought (Access
Hacks) and with my limited knowledge I am having a heck of a time.

I sincerely appreciate your responses.
 
J

John Spencer

If (IF) you are inserting into every field in the table, you are not
required to specify the field list. SQL assumes that you are inserting into
each field in the table and in the order of the fields in the table.

That said, I prefer to specify the fields just to ensure the query will
continue to work if I add a field to the table or if the order of the fields
is changed. Also, I find it much clearer if the target fields are listed.
 
B

Brian Bastl

Hi John,

thanks for the clarification.

Brian

John Spencer said:
If (IF) you are inserting into every field in the table, you are not
required to specify the field list. SQL assumes that you are inserting into
each field in the table and in the order of the fields in the table.

That said, I prefer to specify the fields just to ensure the query will
continue to work if I add a field to the table or if the order of the fields
is changed. Also, I find it much clearer if the target fields are listed.
Click to expand...
 
J

John Vinson

Thanks again John,

For the record when I started this thread I wrote:
-----------------------------------------------------------------
ScheduleID is Date/Time
Scheduled is Text
JobDate is Date/Time
Status is Text
Click to expand...

It would have... had I seen that thread or had you repeated it. Sorry,
I didn't have time to go back to Google Groups for the other.

Did the # delimiters solve *that particular* problem?
Also on my other thread I was not using "ScheduleID" (Date/Time) but
"Schedule" (text) that is why I didn't feel I had to explain it since Duane
told me what I should do.

I am stil having problems, now the eror is
Click to expand...

I suspect that this SQL expression also lacks the required #
delimiters around a date value.
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_end
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
If NewRecord = False Then
ssql = build_sql(ScheduleID, "Update")
Click to expand...

Try setting a breakpoint and see what is actually in ssql at this
point. If the date doesn't have # around it, it should. You may need
to abandon the build_sql method and create the SQL string yourself, or
adjust the build_sql code.



John W. Vinson[MVP]
 
W

Wind54Surfer

Thanks again John,

I now I am getting closer now, but back to the original error:
"Data type mismatch in criteria expression"

----------------------------------------------------------------------------
Function build_sql(Schedule_ID As Date, operation As String) As String
build_sql = "Insert Into tblJobScheduleAuditLog Values (#"
build_sql = build_sql & ScheduleID & "# , "
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & operation & "', "
build_sql = build_sql & "#" & Now() & "#)"
End Function
-----------------------------------------------------------------------
The breakpoint still stops at: "conn.Execute ssql" at Sub below
----------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_end
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
If NewRecord = False Then
ssql = build_sql(ScheduleID, "Update")
conn.Execute ssql
conn.Close
Set conn = Nothing
Else
If IsNull(Scheduled) Or Scheduled = "" Then
MsgBox "Must Enter Schedule"
Cancel = True
End If
End If
Exit Sub
err_end:
MsgBox Err.Description
End Sub
-----------------------------------------------------------------------
Please remember the fields values:
---------------------------
ScheduleID is Date/Time
Scheduled is Text
JobDate is Date/Time <----- Should this have # (I tried but don't know how)
Status is Text
--------------------

Thanks for your patience,
Emilio
:
 
J

John Vinson

Thanks again John,

I now I am getting closer now, but back to the original error:
"Data type mismatch in criteria expression"
Click to expand...

I'm having trouble following through all this code, especially since I
don't know the values of your variables. Could you set a breakpoint in
the code and post the actual value being assigned to the build_sql
string? I'm guessing you have some extra ' marks left in the string,
but can't figure it out!
----------------------------------------------------------------------------
Function build_sql(Schedule_ID As Date, operation As String) As String
build_sql = "Insert Into tblJobScheduleAuditLog Values (#"
build_sql = build_sql & ScheduleID & "# , "
build_sql = build_sql & "'" & _
DLookup("JobDate", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Scheduled", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & _
DLookup("Status", "JobSchedule", "ScheduleID=#" & _
Schedule_ID & "#") & "', "
build_sql = build_sql & "'" & operation & "', "
build_sql = build_sql & "#" & Now() & "#)"
End Function
-----------------------------------------------------------------------
The breakpoint still stops at: "conn.Execute ssql" at Sub below
----------------------------------------------------------------------
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo err_end
Dim ssql As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
If NewRecord = False Then
ssql = build_sql(ScheduleID, "Update")
conn.Execute ssql
conn.Close
Set conn = Nothing
Else
If IsNull(Scheduled) Or Scheduled = "" Then
MsgBox "Must Enter Schedule"
Cancel = True
End If
End If
Exit Sub
err_end:
MsgBox Err.Description
End Sub
-----------------------------------------------------------------------
Please remember the fields values:
---------------------------
ScheduleID is Date/Time
Scheduled is Text
JobDate is Date/Time <----- Should this have # (I tried but don't know how)
Status is Text
--------------------
Click to expand...


John W. Vinson[MVP]
 
W

Wind54Surfer

Hi John,

I fugured it out, took a shorcut, changed the field "JobDate"in the
audittable to Text.

Thanks for all your help, I truly appreciated.

Emilio
 
J

John Vinson

I fugured it out, took a shorcut, changed the field "JobDate"in the
audittable to Text.
Click to expand...

It was either that or (probably better) delimit that field with # as
well.

John W. Vinson[MVP]
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Syntax error in insert into statement 6
Syntax error (missing operator) in query expression 3
Time Scheduling Conflict Check 3
'Data Type Mismatch In Criteria Expression' error when using 'Count' in query Design; Help! 0
Data type mismatch in criteria expression issue 2
Data type mismatch in criteria expression even the datatype is the same 1
Data mismatch error 2
Help with dCount - “Data Type mismatch in criteria expression†2

Top