SQL Query Containing Two Text Variables!

  • Thread starter lovely_angel_for_you
  • Start date
L

lovely_angel_for_you

Hi,
I am trying to get this query work. Please assist me what could be
wrong. The query would be probably wrapped, so please check, its just
one straight line.

I am trying to pull the "City" from the table where "Name" and "Time"
are found. Data is entered on the form on VB. Access is working as
backend.

ChkName = name.text
ChkTime = Time.text 'in format 00:00, 12:30

Set chkset2 = conn.Execute("SELECT city FROM Data Where Name='" &
ChkName & "' AND Time='" & ChkTime)

Whatever I may try, it gives me syntax error.

Please suggest something.

Regards
Lovely
 
D

Dale Fye

You might want to try it like:

Set chkset2 = conn.Execute("SELECT City FROM Data WHERE Name = '" & ChkName
& "' AND Time = #" & chkTime & "#")

HTH
Dale
 
L

lovely_angel_for_you

Returns an Emptry Recordset. When I know the data is available to the
one that I trying to find.

Set rdset = conn.Execute("SELECT City FROM Data Where Name='" &
Chkname & "' AND Time=#" & ChkTime & "#")

Do While Not rdset.EOF
MsgBox rdset("City")

rdset.MoveNext
Loop

The msgbox never pops up.

And Time and Name are both strings.

Any information on the same.

Regards
Lovely
 
S

Smartin

Returns an Emptry Recordset. When I know the data is available to the
one that I trying to find.

Set rdset = conn.Execute("SELECT City FROM Data Where Name='" &
Chkname & "' AND Time=#" & ChkTime & "#")

Do While Not rdset.EOF
MsgBox rdset("City")

rdset.MoveNext
Loop

The msgbox never pops up.

And Time and Name are both strings.

Any information on the same.

Regards
Lovely
Click to expand...

"Time" and "Name" are both reserved words and might cause Access to get
confused when you have columns of the same names. In your query, enclose
them in [square brackets] and see if that works. Also noticed a missing
terminal ";" (may be no big deal).
Set rdset = conn.Execute("SELECT City FROM Data Where [Name]='" &
Chkname & "' AND [Time]=#" & ChkTime & "#;")
Click to expand...

If still no joy, create a new String variable called TheSQL to contain
your SQL statement, Debug.Print it to the immediate window after setting
its value, copy/paste that into a new query window and run it. Does this
return any results?

Dim TheSQL As String
TheSQL = "SELECT City FROM Data Where [Name]='" & _
Chkname & "' AND [Time]=#" & ChkTime & "#;"
Debug.Print TheSQL
Stop
' check the immediate window here. Press Ctrl + G.
' try executing the SQL in a new query
Set rdset = conn.Execute(TheSQL)

Do While Not rdset.EOF
MsgBox rdset("City")

rdset.MoveNext
Loop
Click to expand...


HTH
 
L

lovely_angel_for_you

Still returns the empty record set.

Set rdset = conn.Execute("SELECT City FROM Data Where CustName='" &
Chkname & "' AND CustTime=#" & ChkTime & "#;")

Do While Not rdset.EOF
MsgBox rdset("City")

rdset.MoveNext
Loop

Any suggestions. Basically it just two strings that I need to search
for.

Set rdset = conn.Execute("SELECT City FROM Data Where FirstName='" &
ChkFirst & "' AND LastName='" & ChkLast)

Regards
Lovely
 
L

lovely_angel_for_you

I dont know how, but it works now.

Set rdset = conn.Execute("SELECT casenumber,priority FROM CaseData
Where Rex='" & ChkRexName & "' AND ScheduledTime='" & ChkScheduleTime &
"';")

Gives me the result that I wanted. I think we were just missing ";"
towards the end.

Thanks for the assistance.

Regards
Lovely
 
Top