error when updating records. HELP!

[S]

I have created a database for a competition.

I have a table called
Entries

I have fields called
Entry #
Session
Time


Each Entry # is assigned a Session (1-5). So I am looking to create an
update button in a form to assign a time for each entry #.

So It would find the first entry # in Session 1 and ask me for the Time
(lets say 9:00AM). Based on that time and that each routine is 4 minutes
long, it would automatically update the times for the rest of the entry #’s.

So entry # 002 would now have 9:04AM in the time field

Entry # 003 would now have 9:08AM in the time field.

Etc etc

This is a code someone gave me but I keep getting error message.

Set rec_ses = CurrentDb.OpenRecordset("select session from entries group by
session order by session", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the start time for the session in the input box
strt_tm = InputBox("Specify the start time for " & rec_ses!session)
'this recordset contains all the entries in the current session ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
session=""" & rec_ses!session & """ order by [entry #]", dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Time = DateAdd("n", num_entry * 4, strt_tm)
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you goto next session
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"



I keep getting runtime error '3464'

data type mismatch in criteria expression

on debugging this highlights

Set rec = CurrentDb.OpenRecordset("select * from entries where session=""" &
rec_ses!Session & """ order by entry", dbOpenDynaset)

Any help would be greatly appreciated. But stuck on this for days.

 

[Allen Browne]

The immediate problem of the bad data type could have several causes.

In the Entries table, you have a field named Session. If you open the table
in design view, what data type is this field? Text? Number? If text, the
quotes are right; if Number omit them, and make sure the Session field in
rec_ses is not null

Also, Session is a reserved word, and will cause you problems. You can
probably get around that in this context by adding square brackets and
including the table name.

For debugging purposes, it helps to use a string variable for the SQL
statement. If it fails, you can then open the Immeidate Window (Ctrl+G) and
Debug.Print the string to help spot the problem.

So:
Dim strSql As String
If Not IsNull(rec_ses![Session]) Then
strSql = "SELECT entries.* FROM entries WHERE entries.[session] = "
& rec_ses![Session] & "ORDER BY entries.entry;"
Debug.Print strSql
...

 

[S]

thank you, thank you, thank you.

i renamed the session field and also made it text.

worked like a charm!

The immediate problem of the bad data type could have several causes.

In the Entries table, you have a field named Session. If you open the table
in design view, what data type is this field? Text? Number? If text, the
quotes are right; if Number omit them, and make sure the Session field in
rec_ses is not null

Also, Session is a reserved word, and will cause you problems. You can
probably get around that in this context by adding square brackets and
including the table name.

For debugging purposes, it helps to use a string variable for the SQL
statement. If it fails, you can then open the Immeidate Window (Ctrl+G) and
Debug.Print the string to help spot the problem.

So:
Dim strSql As String
If Not IsNull(rec_ses![Session]) Then
strSql = "SELECT entries.* FROM entries WHERE entries.[session] = "
& rec_ses![Session] & "ORDER BY entries.entry;"
Debug.Print strSql
...

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have created a database for a competition.

I have a table called
Entries

I have fields called
Entry #
Session
Time


Each Entry # is assigned a Session (1-5). So I am looking to create an
update button in a form to assign a time for each entry #.

So It would find the first entry # in Session 1 and ask me for the Time
(lets say 9:00AM). Based on that time and that each routine is 4 minutes
long, it would automatically update the times for the rest of the entry #’s.

So entry # 002 would now have 9:04AM in the time field

Entry # 003 would now have 9:08AM in the time field.

Etc etc

This is a code someone gave me but I keep getting error message.

Set rec_ses = CurrentDb.OpenRecordset("select session from entries group
by
session order by session", dbOpenDynaset)
Do While Not rec_ses.EOF
'you specify the start time for the session in the input box
strt_tm = InputBox("Specify the start time for " & rec_ses!session)
'this recordset contains all the entries in the current session ordered
by entries
Set rec = CurrentDb.OpenRecordset("select * from entries where
session=""" & rec_ses!session & """ order by [entry #]", dbOpenDynaset)
num_entry = 0
Do While Not rec.EOF
rec.Edit
rec!Time = DateAdd("n", num_entry * 4, strt_tm)
rec.Update
rec.MoveNext
num_entry = num_entry + 1
Loop
rec.Close
'you goto next session
rec_ses.MoveNext
Loop
rec_ses.Close
MsgBox "Update completed"



I keep getting runtime error '3464'

data type mismatch in criteria expression

on debugging this highlights

Set rec = CurrentDb.OpenRecordset("select * from entries where session="""
&
rec_ses!Session & """ order by entry", dbOpenDynaset)

Any help would be greatly appreciated. But stuck on this for days.

 

[Allen Browne]

Excellent: solved.

You probably realize that Text fields sort differently from numbers?
It sorts character-by-character, so 11 sorts before 2.

When you are desigining tables, here's a list to refer to of the names to
avoid:
http://allenbrowne.com/AppIssueBadWord.html