ASP syntax when Access field has # 1

[riley3]

Hello,
I'm trying to read an Access database that has a field named "Contract#" using an ASP coded web page but keep getting a syntax error. This is a database associated with a purchased application so we don't have control over the naming conventions. Is there some way this field can be read with ASP even with the special character?
Thanks, Riley

 

[gmmastros]

Use square brackets around the field name in your queries.

Ex:

Select [!][[/!]Contract#[!]][/!], Field2
From   TableName

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[riley3]

Thanks for the help - worked just fine. Riley

 

[riley3]

I may have spoke to soon. I got rid of one error message but now there is a new one. I've been looking at it for a while but can't see the error.
===================================
strSQL = "SELECT [tblContractCustomers.Contract#] AS cno, "
strSQL = strSQL & "tblContractCustomers.AccountNumber AS cacctno, "
strSQL = strSQL & "tblContracts.Description AS cdesc, "
strSQL = strSQL & "tblContracts.DateWritten AS datewritten, "
strSQL = strSQL & "tblContracts.StartDate AS startdate, "
strSQL = strSQL & "tblContracts.ExpirationDate AS expdate, "
strSQL = strSQL & "tblContracts.ContractHours AS chours, "
strSQL = strSQL & "tblContracts.ContractMinutes AS cmin, "
strSQL = strSQL & "tblContracts.HoursRemaining AS hremain, "
strSQL = strSQL & "tblContracts.MinutesRemaining AS mremain, "
strSQL = strSQL & "tblAccounts.AccountNumber AS anum, "
strSQL = strSQL & "tblAccounts.AccountName As cust "

strSQL = strSQL & "FROM tblAccounts INNER JOIN ( tblContractCustomers "
strSQL = strSQL & "INNER JOIN tblContracts "
strSQL = strSQL & "ON [tblContractCustomers.Contract#] = tblContracts.ContractNumber ) "
strSQL = strSQL & "ON tblAccounts.AccountNumber = tblContractCustomers.AccountNumber "
strSQL = strSQL & "WHERE tblContracts.HoursRemaining > '" & strZero & "' "
strSQL = strSQL & "OR tblContracts.MinutesRemaining > '" & strZero & "' "
strSQL = strSQL & "ORDER BY tblContracts.ContractNumber DESC "
==============================
The error is - Invalid bracketing of name '[tblContractCustomers.Contract#]'.
- it's in the ON statement but I can't see a problem. Thanks for any additional help you may be able to give me.
Riley

 

[DotNetGnat]

do this:

tblContractCustomers.[Contract#]

-DNG

 

[gmmastros]

[tblContractCustomers.Contract#] is wrong.

Use:

tblContractCustomers.[!][[/!]Contract#[!]][/!]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[riley3]

That did it. The error in question went away. I still have an error, unrelated. It's a "mismatch" error and a different problem. I have these all the time and will deal with it later. Thanks again for the help.
Riley