excel vba query to get data from access table between two number in a column

[sathyaganapathi]

I have a access table and one column contains week numbers as yyww (ex. 2133, 2134, 2135 etc..). I want to write a excel vba query to get the table data between two numbers in specific column (for example - 2133 and 2135). Please help with VBA code.

the query I used is as below. But I do not get the data

SWeek = SWeek.value
EWeek = EWeek.value
qry = "SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null order by [Month], [Week], [CountOfReason] AND [Week] BETWEEN SWeek AND EWeek"

If I use below code, I get all data in table.

qry = "SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null order by [Month], [Week], [CountOfReason]"

how to add the week number to the query? please help.

 

[Micron]

Not sure if this is the reason or not, but always put your sort at the end. Try

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND [Week] BETWEEN SWeek AND EWeek order by [Month], [Week], [CountOfReason]"
Words like MONTH are reserved words and should not be used for objects. Suggest you bookmark
ReservedWords

 

[sathyaganapathi]

Not sure if this is the reason or not, but always put your sort at the end. Try

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND [Week] BETWEEN SWeek AND EWeek order by [Month], [Week], [CountOfReason]"
Words like MONTH are reserved words and should not be used for objects. Suggest you bookmark
ReservedWords

Hi Microns,
Thanks for the inputs. I tried with the code below and it did not work.

qry = "SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND [Week] BETWEEN SWeek AND EWeek order by [Week], [CountOfReason]"

As you suggested I removed the "Month" from the query. I will try to use different word for it layer.
Is this applicable for "Week" also?

 

[Micron]

"did not work" means what? Error message? No records at all? Records but wrong or missing?
Are you sure you have records that satisfy all 3 criteria? Maybe open your table and try to apply your criteria as filters
- or at least review the table records to ensure some of them meet the criteria
- or build a test query with 1 piece of criteria at a time (e.g. first where department is not null, then remove that and try the between dates).
Are any of these fields are table level lookup fields?

Is this applicable for "Week" also?

Is it in the list at the link I gave you? If you're asking, you must not have reviewed it when you could find the answer to that.

 

[sathyaganapathi]

"did not work" means what? Error message? No records at all? Records but wrong or missing?
Are you sure you have records that satisfy all 3 criteria? Maybe open your table and try to apply your criteria as filters
- or at least review the table records to ensure some of them meet the criteria
- or build a test query with 1 piece of criteria at a time (e.g. first where department is not null, then remove that and try the between dates).
Are any of these fields are table level lookup fields?


Is it in the list at the link I gave you? If you're asking, you must not have reviewed it when you could find the answer to that.

Hi Micron,
Sorry for my bad English.. I try to give answer more clearly as much as possible with my very little knowledge on access table and excel VBA.

'did not work' means the query did not fetch any records from the access table. Only the table header line appears.
Before applying the VBA query I tried the filters for 'week' in access table and it showed the correct records. The 'Depertment is not null' is giving the records from the table with all departments in it, eliminating the blank.
I will try once again the combinations you mentioned and get back.

I did not use any lookup fields.

If I remove the week part from the query it fetches all records from the table as I mentioned earlier. But, i need the records related to specific weeks from the table. That is the reason asked. If it is the problem I will rename it in access table and try again.

 

[Micron]

OK, so you wrote that this returns records
"SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null order by [Month], [Week], [CountOfReason]"

what about

"SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN SWeek AND EWeek" ?
If no records, then there is either something wrong with what is contained by SWeek or EWeek (or both of them) - or there could be an issue with the data type. If there are records with 2133 and 2134 try

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN 2133 AND 2134
If that errors or doesn't return any records then try

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN '2133' AND '2134'
If that works, then the issue is that Week values are text, not numbers. Easy enough to fix, I think.

 

[sathyaganapathi]

OK, so you wrote that this returns records
"SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null order by [Month], [Week], [CountOfReason]"

what about

"SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN SWeek AND EWeek" ?
If no records, then there is either something wrong with what is contained by SWeek or EWeek (or both of them) - or there could be an issue with the data type. If there are records with 2133 and 2134 try

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN 2133 AND 2134
If that errors or doesn't return any records then try

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN '2133' AND '2134'
If that works, then the issue is that Week values are text, not numbers. Easy enough to fix, I think.

Dear Micro, Thanks again for the detailed input.

Below are the combinations checked and results.

"SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null order by [Month], [Week], [CountOfReason]"
Result: ok. returned the full set of records, including all weeks in the table.

"SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN SWeek AND EWeek"
Result: No records displayed. Only header line displayed.

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN 2133 AND 2134
Result: retuened the records between 2133 and 2134. Checked for week between 2134 and 2135. returned records between 2134 and 2135.
Also checked for week between 2133 and 2135. returned records between 2133 and 2135.

SELECT * FROM [Countdeptbyweek] WHERE [Week] BETWEEN '2133' AND '2134'
Result: No records displayed. Only header line displayed.

Looks like there is some problem with SWeek and EWeek. I tried with below to check the input values.

SWeek = HoldSummary.SWeek.value
EWeek = HoldSummary.EWeek.value

Msgbox retuend - SWeek: 2134 and EWeek: 2134
This shows that the input is number. right?

SWeek1 = Format(HoldSummary.SWeek1.value, Number = "0")
EWeek1 = Format(HoldSummary.EWeek1.value, Number = "0")
Msgbox returned - SWeek1: FaI0e and EWeek1: FaI0e.

Got stuck here. I cant understand why this error. I tried to search in internet and did not find any clue.

 

[Micron]

I'm beginning to think I'm an idiot. I've been looking at that as sql, not vba. Even then I've been missing the boat, so to speak. Try

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null  AND [Week] BETWEEN " & SWeek & " AND " & EWeek & " Order By [Month], [Week], [CountOfReason]"

It is good to always assign sql to a variable when doing code so that you can debug.print and examine it as a troubleshooting method. Since I don't have your tables r your code, I can only code to build a string and print it, so I did this:

Function testSql(eweek As Integer, sweek As Integer)
Dim sql As String

sql = "SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND "
sql = sql & "[Week] BETWEEN " & sweek & " AND " & eweek & " Order By "
sql = sql & "[Month], [Week], [CountOfReason]"
Debug.Print sql

End Function

which printed

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND [Week] BETWEEN 2144 AND 2133 Order By [Month], [Week], [CountOfReason]

 

[sathyaganapathi]

I'm beginning to think I'm an idiot. I've been looking at that as sql, not vba. Even then I've been missing the boat, so to speak. Try

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null  AND [Week] BETWEEN " & SWeek & " AND " & EWeek & " Order By [Month], [Week], [CountOfReason]"

It is good to always assign sql to a variable when doing code so that you can debug.print and examine it as a troubleshooting method. Since I don't have your tables r your code, I can only code to build a string and print it, so I did this:

Function testSql(eweek As Integer, sweek As Integer)
Dim sql As String

sql = "SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND "
sql = sql & "[Week] BETWEEN " & sweek & " AND " & eweek & " Order By "
sql = sql & "[Month], [Week], [CountOfReason]"
Debug.Print sql

End Function

which printed

SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null AND [Week] BETWEEN 2144 AND 2133 Order By [Month], [Week], [CountOfReason]

Dear Micron,
The below code you provided made my day.... The results are perfect and could able to get records for the different week combination.

"SELECT * FROM [Countdeptbyweek] WHERE [Department] is not null  AND [Week] BETWEEN " & SWeek & " AND " & EWeek & " Order By [Month], [Week], [CountOfReason]"

I shall try the sql code you mentioned. I am new to that. I may take some time to understand that.
However, a BIG thanks for your support !!!
best regards..

 

[Micron]

I'm sorry it took me so long to see the problem. It must be a symptom of getting older because I should have seen the proper solution right away.