Converting Queries To Code: Yet again

[Mike Wilson]

Sorry to keep on with these type of questions.

Could anybody give me any ideas on how to get an Insert Into
to work

DoCmd.Run Sql "INSERT INTO ExportUPS ( OrderNo, Company, Contact, Add1,
Add2, Add3, Add4, PostCode, Country, TelNo, EMail )
SELECT Export.OrderNo, Export.Company, Export.Contact, Export.Add1,
Export.Add2, Export.Add3, Export.Add4, Export.PostCode, Export.Country,
Export.TelNo, Export.EMail
FROM Export WHERE (((Export.ShipMethod) Like "UPS****"))"

The problem I am having is to find a replacement for the Like "UPS****"
portion of the code.

The " are interpretated as the end of the SQL in the code whereas in the
the query they are there to qualify the like parameter.

Any assistance much appreciated


Thanks

Mike

 

[Duane Hookom]

Change to
FROM Export WHERE (((Export.ShipMethod) Like 'UPS****'))"

 

[Mike Wilson]

Thank you, that works.

I tried what I thought to be all combinations, Is there any
reference that I can get hold of that would list the statements ?

Many thanks for your help

Mike

 

[Howard Brody]

I always build the SQL statement first and then run it with the RunSQL command.

As for your WHERE statement: I assume all the asterisks after UPS are wildcards. You only need one (the asterisk represents any number of characters). You should also use single quotes within your statement (since it's already within double quotes). If you're having a problem with the syntax, build the query in regular QueryDesignView and just copy the SQL into the VB pane.

I'd try something like this

Dim strI As Strin
Dim strS As Strin
Dim strF As Strin
Dim strW As Strin
Dim strSQL as Strin

strI = "INSERT INTO ExportUPS ( OrderNo, Company, Contact, Add1, "
& "Add2, Add3, Add4, PostCode, Country, TelNo, EMail )
strS = "SELECT Export.OrderNo, Export.Company, Export.Contact, "
& "Export.Add1, Export.Add2, Export.Add3, Export.Add4, Export.PostCode, "
& "Export.Country, Export.TelNo, Export.EMail
strF = "FROM Export
strW = "WHERE (((Export.ShipMethod) Like 'UPS*'))
strSQL = strI & strS & strF & str

DoCmd.RunSQL strSQ

Hope this helps

Howard Brod



----- Mike Wilson wrote: ----

Thank you, that works

I tried what I thought to be all combinations, Is there an
reference that I can get hold of that would list the statements

Many thanks for your hel

Mik

 

[Howard Brody]

One more thing I forgot

In Access, a SQL string always needs to end with a semi-colon. So the code should be

Dim strI As Strin
Dim strS As Strin
Dim strF As Strin
Dim strW As Strin
Dim strSQL as Strin

strI = "INSERT INTO ExportUPS ( OrderNo, Company, Contact, Add1, "
& "Add2, Add3, Add4, PostCode, Country, TelNo, EMail )
strS = "SELECT Export.OrderNo, Export.Company, Export.Contact, "
& "Export.Add1, Export.Add2, Export.Add3, Export.Add4, Export.PostCode, "
& "Export.Country, Export.TelNo, Export.EMail
strF = "FROM Export
strW = "WHERE (((Export.ShipMethod) Like 'UPS*'));
strSQL = strI & strS & strF & str

DoCmd.RunSQL strSQ

Hope this helps

Howard Brod

 

[Chris]

They don't have to...

It will run just fine either way.


And to answer the first problem:

The correct SQL syntax for a LIKE clause is:

Where FieldName LIKE "UPS*"

The problem is that you are trying to use a String to
represent the SQL line.

strSQL = "Select * from TableName where FieldName
LIKE "UPS*""

The Quotes (") seperate what is inside the string to what
is outside the string. There are several possible ways to
overcome this. The first is to use single quotes, as they
are also acceptable:

Where FieldName Like 'UPS*'

which converts to:


strSQL = "Select * from TableName where FieldName
LIKE 'UPS*'"

Another possible way is to double up the quotes..

strSQL = "Select * from TableName where FieldName
LIKE ""UPS*"""

Access will treat 2 doublequotes as 1 doublequote inside
the string.



Chris

 

[Mike Wilson]

Thanks everybody, very grateful for all your hel

Mike