Creating a QueryDef

[Perissos]

Ok... I am lost. I know how to create a querydef, but I have never passed a string of variables to select multiple records before. I tried creating an array, but even though the array populated, the querydef would not recognize it. I tried creating a string, but it recognizes it as a field and not a value within the field... so I know I am doing something wrong and its probably something easy...so I am reaching out for help.

I know in a query you can do something like this
Select * from table where statevalue in("FL","VA","TN")

So when I set those values to a string, why would it recognize it as a field when I put it like this

Set qdf = db.CreateQueryDef("qrystcompare","Select Vamt from Stbl where Stcode in(str)")

 

[Perissos]

ok.. I am getting closer. I changed it to
Set qdf = db.CreateQueryDef("qrystcompare","Select Vamt from Stbl where Stcode in '" & (str) & "')

I just need to add the () around the name. But in debug mode i noticed that the last state had a , after it. so I may be having a problem creating the string. I may not know how to fix it yet, but at least I know how to find the problem.

This is how I am setting the string
str = str & Me(textcontrol).Caption & ","

 

[Galaxiom]

Set qdf = db.CreateQueryDef("Select Vamt from Stbl where Stcode in('" & str & "')")

 

[Perissos]

cool..thank you. After all the querydefs I written over the last two months..you would have thought I would know that by now.

I tried that and even though no records where returned, at least I didn't get an error. When I went to look at why no records where returned the query showed the selection like this.

In ('FL,')

Thank you so much.

The good news is... after next week, I probably won't have the 12-16 hour days and my brain(and eyes and back) can have a regeneration period.

 

[Galaxiom]

When I went to look at why no records where returned the query showed the selection like this.

In ('FL,')

Use single quotes inside your string. The double quotes are getting mixed up with the double quotes in the command.

 

[Perissos]

Hot Dog... I GOT IT!!!

For the string I did this
If Me(checkcontrol).VALUE = True Then
If itemcount = 1 Then
str = str & "'" & Me(textcontrol).Caption & "'"
Else
str = str & "," & "'" & Me(textcontrol).Caption & "'"
End If


and then for the other portion I did this
In(" & str & ")

and it works great.

There has got to be a better way of doing this... for later though

Thanks again... now I can get some sleep!

 

[namliam]

        If itemcount = 1 Then
            str = str & "'" & Me(textcontrol).Caption & "'"
        Else
            str = str & "," & "'" & Me(textcontrol).Caption & "'"
        End If
and then for the other portion I did this
        In(" & str & ")

This would be more commonly solved:

            str = str & "," & "'" & Me(textcontrol).Caption & "'"
and then for the other portion do this
        In(" & mid(str,2) & ")

Also the Me() syntax can be replaced by Me.textcontrol, this will reduce the number of typo's ...