VBA Syntax MS Access 2013: (Correct datatype and syntax for casting/conversion) for use in WHERE criteria statement in SELECT FROM WHERE string SQL query

Currently having an issue while using string variable TSx=arrayREP(0, i) for WHERE criteria statement in SELECT FROM WHERE string SQL query.

 TSx = ("criteriax") because Dim variable As String and I need [(criteriax)-returned value from array not wrapped in " "] for query to act properly.

Dim TSx As String
Dim QRYx As String

TSx = arrayREP(0, i)
QRYx = "SELECT tbl.field1, tbl.field2, tbl.field3, tbl.field4, tbl.field5, tbl.field6, tbl.field7,FROM tbl WHERE tbl.field1=TSx"

Any suggestions?

Box asking for parameters for TSx appears after running. If I enter the value for TSx without the " " wrapped around it from using string variable everything else works perfectly.

I've been self-teaching over the past two days and so far this is only trouble I'm having, probably because its within a For Next Loop. Hopefully just a minor fix/conversion.

Thanks!
Cole
yop6Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
WHERE tbl.field1=" & chr(34) & TSx & chr(34)

If TSx does not contain any single quotes, you can use:

WHERE tbl.field1='" TSx & "'"

here we have = ' " TSx & " ' " in expanded for for you to see single quotes and double quotes.

But, if you expect to have single quotes like TSx "O'Donald" then use of chr(34) is required.
Rey Obrero (Capricorn1)Commented:
where is you code for the loop?

why not just pass the element of the array to the query?

what DATA type is the field you are filtering, Number or Text?

how about making Tsx as variant
Dim TSx
Dim QRYx As String

 TSx = arrayREP(0, i)

what value do you get for TSx?



.
yop6Author Commented:
@Mike

I may have been unclear. TSx currently returns "75-X-4553" in first loop from 0 to 99 i=0 because its a string. I know i can't use it for my Criteria because the return I'm looking for in tbl.field1 is 75-X-4553.

Thanks
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

yop6Author Commented:
@Ray
Dim VBA_16 As DAO.Database
Set VBA_16 = CurrentDb


Dim arrayREP() As Variant
Dim rstREP As DAO.Recordset
Set rstREP = VBA_16.OpenRecordset("SELECT TSx, QRYx, PATHx FROM tblREP", dbOpenDynaset)

With rstREP
    .MoveLast
    .MoveFirst
    arrayREP = rstREP.GetRows(.RecordCount)
End With

rstREP.Close
    Set rstREP = Nothing
   
'Looping through rstGWA using arrayREP for variables
Dim rstGWA As DAO.Recordset
Set rstGWA = VBA_16.OpenRecordset("Qry_GWA")

For i = 0 To 99
Dim TSx As String
Dim PATHx As String
Dim QRYx As String
Dim qdfQRY As QueryDef

i = 0
TSx = arrayREP(0, i)
PATHx = arrayREP(2, i)


QRYx = "SELECT Qry_GWA.[Treasury Symbol], Qry_GWA.Authority, Qry_GWA.Transfers, Qry_GWA.GrossDisb, Qry_GWA.OffsetCollect, Qry_GWA.EndBal, Qry_GWA.BegBal FROM Qry_GWA WHERE Qry_GWA.[Treasury Symbol] =TSx"

Set qdfQRY = VBA_16.CreateQueryDef("", _
QRYx)

DoCmd.TransferSpreadsheet acExport, 10, "qdfQRY", PATHx, False, "DataGWA"

Next i

rstGWA.Close
    Set rstGWA = Nothing



Any suggestions on how to clean this up or improve performance would be appreciated as well. I'm pretty green.

Thanks!
Rey Obrero (Capricorn1)Commented:
< TSx currently returns "75-X-4553" in first loop from 0 to 99 i=0 because its a string. I know i can't use it for my Criteria because the return I'm looking for in tbl.field1 is 75-X-4553. > 

copy and paste the statement below

QRYx = "SELECT Qry_GWA.[Treasury Symbol], Qry_GWA.Authority, Qry_GWA.Transfers, Qry_GWA.GrossDisb, Qry_GWA.OffsetCollect, Qry_GWA.EndBal, Qry_GWA.BegBal FROM Qry_GWA WHERE Qry_GWA.[Treasury Symbol] ='" & TSx &"'"

Open in new window



post back the result
yop6Author Commented:
TSx Parameter Box.

WHERE Qry_GWA.[Treasury Symbol] ='75-X-4553'

Double quotations were replaced with single quotations. Any way to remove single quotations?
Rey Obrero (Capricorn1)Commented:
@yop6,
<Double quotations were replaced with single quotations. Any way to remove single quotations? >
the single quotations MUST remain to make your query work.

your problem lies in the succeding lines

Set qdfQRY = VBA_16.CreateQueryDef("", _
 QRYx)

 DoCmd.TransferSpreadsheet acExport, 10, "qdfQRY", PATHx, False, "DataGWA"

Open in new window


you can not pass a query def to an export command, you have to pass the a SAVED query name.

to do this, create a query with this statement, save the query as myQuery

QRYx = "SELECT Qry_GWA.[Treasury Symbol], Qry_GWA.Authority, Qry_GWA.Transfers, Qry_GWA.GrossDisb, Qry_GWA.OffsetCollect, Qry_GWA.EndBal, Qry_GWA.BegBal FROM Qry_GWA WHERE Qry_GWA.[Treasury Symbol] ='" & TSx &"'"

Open in new window


open the query and see the result..


post back the result... if the result is good we will proceed with your looping codes with altering the query def statement


.
yop6Author Commented:
could you give me syntax for saving the query as my query
Rey Obrero (Capricorn1)Commented:
place this code in a regular module and run

Sub TestQdf()
Dim QRYx As String
Dim qdfQRY As DAO.QueryDef
Dim VBA_16 As DAO.Database
 Set VBA_16 = CurrentDb


QRYx = "SELECT Qry_GWA.[Treasury Symbol], Qry_GWA.Authority, Qry_GWA.Transfers, Qry_GWA.GrossDisb, Qry_GWA.OffsetCollect, Qry_GWA.EndBal, Qry_GWA.BegBal FROM Qry_GWA WHERE Qry_GWA.[Treasury Symbol] ='" & TSx &"'"

Set qdfQRY = VBA_16.CreateQueryDef("myQuery", QRYx)

 DoCmd.openquery "myQuery"

 end sub

Open in new window

yop6Author Commented:
OK, that did exactly what I needed it to do. Thanks! Now after my DoCmd. for exporting it seems like I will have to either delete the query or rename it temporarily as I'm looping.
Rey Obrero (Capricorn1)Commented:
you can delete the query then recreate

place this inside your loop

QRYx = "SELECT Qry_GWA.[Treasury Symbol], Qry_GWA.Authority, Qry_GWA.Transfers, Qry_GWA.GrossDisb, Qry_GWA.OffsetCollect, Qry_GWA.EndBal, Qry_GWA.BegBal FROM Qry_GWA WHERE Qry_GWA.[Treasury Symbol] ='" & TSx &"'"

if dcount("*","msysobjects","[name]='myQuery'") > 0 then
     docmd.delateobject acquery, "myQuery"
end if

Set qdfQRY = VBA_16.CreateQueryDef("myQuery", QRYx)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
yop6Author Commented:
Extremely helpful!
Rey Obrero (Capricorn1)Commented:
another way is to just change t the SQL statement of the the saved query..
if you want to tackle this.. post another q.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.