Variable in VBA from Query Result??

[liammoohan]

I have a query which returns a distinct week number from a table i.e. 48

Is there a way that I can use this result as a variable in VBA which is used to save the results in a table within the database as a CSV file for use outside the database?

 

[Joe4]

Does your query just return one single record with that single field?

Can you explain your second line to us in more detail? I find it a bit confusing exactly how you want to use this.
Can you show us where the "48" is to come into play in your VBA code?

 

[Micron]

Sounds like 48 is calculated, which may be an example of a case where you shouldn't be storing calculations in a table. If it's not calculated by the query, then it exists as data so why would you want to repeat this in another table? Two possible issues there.
If you're going to append/update variable values to a table, you'll need a recordset or will need to execute the update/append in code.

 

[liammoohan]

Does your query just return one single record with that single field?

Can you explain your second line to us in more detail? I find it a bit confusing exactly how you want to use this.
Can you show us where the "48" is to come into play in your VBA code?

In the attached image this shows the VB module I have running which creates the file' in the data table there is a column which has the week number and for the circled WKXX I want to be able to have the module run and input the week number as opposed to the XX when naming the file.

Is there a way to do this when I create a query as below to get the week number from the data and use the results of the query written as the variable for populating the week number?

SELECT DISTINCT WeekNumber
From DataTable

 

[Joe4]

You still haven't answered my first question. Without seeing your data, we have no idea what your query is returning.

So, does this query always return exactly one record, or might it return multiple?

SELECT DISTINCT WeekNumber
From DataTable

 

[liammoohan]

You still haven't answered my first question. Without seeing your data, we have no idea what your query is returning.

So, does this query always return exactly one record, or might it return multiple?

SELECT DISTINCT WeekNumber
From DataTable

See attached image showing query as written on left and how the result is displayed on the right.

I want to use the query result (shown on right of image) to be the variable in the VBA that will be used to replace the XX as previously highlighted

 

[Joe4]

OK, this thread here shows multiple ways to get the value from the query into VBA (including using Recordsets and DLOOKUP).
I think you should be able to incorporate one of these methods in your VBA code.

How do I assign the result of SELECT query to a variable in VBA for MSAccess?

social.msdn.microsoft.com

 

[liammoohan]

OK, this thread here shows multiple ways to get the value from the query into VBA (including using Recordsets and DLOOKUP).
I think you should be able to incorporate one of these methods in your VBA code.

How do I assign the result of SELECT query to a variable in VBA for MSAccess?

social.msdn.microsoft.com

Having already found and tried what is in that thread myself with no luck before posting on here I will continue to look elsewhere to try and find a solution.

Thanks

 

[Joe4]

Having already found and tried what is in that thread myself with no luck before posting on here I will continue to look elsewhere to try and find a solution.

Thanks

If you post the code you tried, we may be able to help you fix it up to get it to do what you want.

Actually, I am thinking that if you use the DLOOKUP method, you may not need the query at all. You can probably do a DLOOKUP directly into your Calendar table, using the current date as your criteria.
Have you tried that?

If you are having troubles getting it to work, can you post a small sampling of data from your Calendar table for us to see?
I want to make sure that it is set up the way I think it is, and there is nothing funny about it that might be causing issues.

 

[liammoohan]

If you post the code you tried, we may be able to help you fix it up to get it to do what you want.

Actually, I am thinking that if you use the DLOOKUP method, you may not need the query at all. You can probably do a DLOOKUP directly into your Calendar table, using the current date as your criteria.
Have you tried that?

If you are having troubles getting it to work, can you post a small sampling of data from your Calendar table for us to see?
I want to make sure that it is set up the way I think it is, and there is nothing funny about it that might be causing issues.

This is what I have found on another thread and it is now working:

Dim WKno As String

Dim RS As DAO.Recordset
Set RS = CurrentDb.OpenRecordset("SELECT DISTINCT WeekNumber FROM tblCalendar WHERE CallDate = Date()")
If Not RS.EOF Then
WKno = Nz(RS("WeekNumber"))
End If
RS.Close
Set RS = Nothing