sql select statement not working

[Dale]

I cannot seem to figure out what is wrong with my select
statement. It seems so simple, yet it escapes me.
I am taking information from a custom form i.e. order
number and after connecting to a SQL database want to
select that order form the database.
Here is the code:

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & OrderNo

ord = database table name
[OrdNum] = database field name
OrderNo = filed(control) from custom form

Your help is appreciated and thanks you in advance.

Dale

 

[Sue Mosher [MVP-Outlook]]

Is OrderNo a numeric or text field? If text, it needs single quotes around
it in the WHERE string.

 

[Dale]

-----Original Message-----
what is the data type of the "OrderNo" variable? Do you

need to include quotes around the value?

----- Dale wrote: -----

I cannot seem to figure out what is wrong with my select
statement. It seems so simple, yet it escapes me.
I am taking information from a custom form i.e. order
number and after connecting to a SQL database want to
select that order form the database.
Here is the code:

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & OrderNo

ord = database table name
[OrdNum] = database field name
OrderNo = filed(control) from custom form

Your help is appreciated and thanks you in advance.

Dale

.
Sorry, The data type of the OrderNo field/control is

Text. Wouldn't putting quotes around the OrderNo filed
make it a literal.

 

[Dale]

-----Original Message-----
Is OrderNo a numeric or text field? If text, it needs single quotes around
it in the WHERE string.

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

I cannot seem to figure out what is wrong with my select
statement. It seems so simple, yet it escapes me.
I am taking information from a custom form i.e. order
number and after connecting to a SQL database want to
select that order form the database.
Here is the code:

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & OrderNo

ord = database table name
[OrdNum] = database field name
OrderNo = filed(control) from custom form

Your help is appreciated and thanks you in advance.

Dale

.
So it would now look like this. and yes it is a text

field.

strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & 'OrderNo'

If your answer is yes then something else is wrong. I
receive a syntax error.

 

[Dale]

-----Original Message-----
The single quotes go around the value, concatenated into the string:

strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & Chr(39) & OrderNo & Chr(39)

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

So it would now look like this. and yes it is a text

field.

strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & 'OrderNo'

If your answer is yes then something else is wrong. I
receive a syntax error.

-----Original Message-----
Is OrderNo a numeric or text field? If text, it needs single quotes around
it in the WHERE string.

I cannot seem to figure out what is wrong with my select
statement. It seems so simple, yet it escapes me.
I am taking information from a custom form i.e. order
number and after connecting to a SQL database want to
select that order form the database.
Here is the code:

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & OrderNo

ord = database table name
[OrdNum] = database field name
OrderNo = filed(control) from custom form

Your help is appreciated and thanks you in advance.

Dale


.

.
Got it now thanks so much

 

[Sue Mosher [MVP-Outlook]]

The single quotes go around the value, concatenated into the string:

strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & Chr(39) & OrderNo & Chr(39)

--
Sue Mosher, Outlook MVP
Author of
Microsoft Outlook Programming - Jumpstart for
Administrators, Power Users, and Developers

So it would now look like this. and yes it is a text

field.

strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & 'OrderNo'

If your answer is yes then something else is wrong. I
receive a syntax error.

-----Original Message-----
Is OrderNo a numeric or text field? If text, it needs single quotes around
it in the WHERE string.

I cannot seem to figure out what is wrong with my select
statement. It seems so simple, yet it escapes me.
I am taking information from a custom form i.e. order
number and after connecting to a SQL database want to
select that order form the database.
Here is the code:

Set rstOrd = CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM ord WHERE [OrdNum] = " & OrderNo

ord = database table name
[OrdNum] = database field name
OrderNo = filed(control) from custom form

Your help is appreciated and thanks you in advance.

Dale

.