Help with grabbing data from query into variable

[pjmatelli]

Hi all,
I don't even know how to search for this one. What I need to do is run a query and then take the value(s) in one field for each record and put them into a variable to be used later. Hope that makes sense...here's an example

Field 1
2456, 2938
5432
1234

Result
"2456, 2938, 5432, 1234"

How do I get to that result? I have tried many different ways of looping and I can't seem to get the code to grab the data correctly. Any help, as always, is greatly appreciated.

TIA

 

[xenou]

It sort of depends on what you want to do with the variables. If the values are query-able then you can really just query for them when you need them. If you need/prefer to store them I'd just open a recordset. Then they will be in your recordset. The rs variable should be outside the subs (at the top) so it will persist.

Private rs As DAO.Recordset
[COLOR="Navy"]Sub Foo[/COLOR]()
    Set rs = currentDB.OpenRecordset("Select Field1 From Table1", dbOpenDynaset)
[COLOR="Navy"]End Sub[/COLOR]
[COLOR="Navy"]Sub Bar[/COLOR]()
    If not rs is Nothing then
        rs.MoveFirst
        Do While Not rs.EOF 
            debug.print rs.fields(0).Value
            rs.MoveNext
        Loop
    End If
[COLOR="Navy"]End Sub[/COLOR]

All things being equal, I should try to work with the data in the table where it is stored rather than trying to keep it in a variable. you can always get data from your tables!

 

[pjmatelli]

Thank you for your response. I need to populate the variable so that I can use that variable within an IN statement in my SQL to get the records to match. The biggest problem is that one table has single data in a field and I need to get it to match to another table with multiple data in a field. The only way to do this [as I understand it] is to run an SQL statement with "IN ("value1", "value2") and I am trying to build the Values from multiple records from the table that can have multiple values in the one field. Here is an example:

In my receiver table, I have a ReceiverNo field that has one piece of data, i.e. 36542.
In my reconciliation table, I have an invoice that can pay multiple receivers and that ReceiverNo field can look like this: 36542, 36543, 36544.

In the result set, I need to pull only the records from the receiver table where the receiver number of the receiver table is in the receiver number of the reconciliation table.

Hope that clarifies - and if there is something I am missing or another way to do this, that information would be greatly appreciated. Thanks so much!

 

[xenou]

Could you use a "LIKE" query then? It sounds like that is what you need rather than an "IN". Are you having trouble with the query or building a SQL string in VBA? Or both?

Query structure:

SELECT Field1, Field2, Field3 
FROM Table1 
WHERE Table1.Field3 LIKE "*36542*"

 

[pjmatelli]

I am having trouble with both I would say...and possibly explaining what I need to do.

WHERE Table1.Field3 LIKE "*36542*" - I need to replace the "*36542*" with a field where the data exists and that's the problem whether I do a like or an IN...

Here's my SQL as it stands now:
sqltext2 = "SELECT * FROM [qryLawsonUpload-Step2] WHERE [qryReceivedAllLawson.ReceiverNo] In ([qryLawsonUpload-Step1.ReceiverNo])"
DoCmd.RunSQL sqltext2

And I get a RunTime error 2342 "A RunSQL action requires an argument consisting of an SQL Statement"

I am assuming because it doesn't like what's in the parentheses after the IN clause, because I use this same statement in another part of my database, but I am building my list in a variable and using the variable in the IN statement and that one works just fine. Hope that helps explain my problem just a little bit more.

Thanks so much for looking into this!

 

[Norie]

Paula

Have you considered not having multiple pieces of data in one field?

That just isn't a good idea, and just causes problems, as I think you've found out.

As for that last piece of code, RunSQL is really only used for 'action' queries, eg UPDATE, DELETE etc

 

[pjmatelli]

Norie,
I wish I could get the client to not use multiple values in one field, but can't. Didn't know the 'thing' about the action queries...thanks.

 

[Norie]

Paula

Can you change the data, or even copy it and change it for your purposes?

What I'm thinking of is you might be able to get the data into a format that is far easier to work with.

It would probably be best done using code, but could be possible using queries, but whatever method you used it really would make like easier.

Also, if this is going to be a regular thing, setting up the code or queries would be a one-off thing that you could just run against the data whenever needed.

 

[xenou]

I think you'd have script this - just don't see how to join the data otherwise, if its coming from two tables/queries.

It's a little confusing to me keeping this straight. I think some sample data would help, with the table/query names, the fields names, and also what you want to do with the results (view them, use them in another query, etc). you said earlier you had a reconciliation table and a receiver table, but your query now is querying queries, not tables...

 

[pjmatelli]

Update:
We are trying, as we speak, to get the data (via code) into a format that can be used and connected. Will update as we accomplish this tedious task. Thanks for all the input, it is truly appreciated.