Help with SQL statement Please

[JFK1965]

I've gone brain dead please help


I have a table calles weeks with the following fields
Month,Month Start,Month End, Year

Month is set as text type while all the others are set as Integer Type.

I'm trying to extract the month where a variable called wks (integer type) is > = month start and <= month end and yrs = year(yrs set as integer type as well). I have the following SQL at the moment

I have to use the Val otherwise i get a data type mismatch error though why I can't work out.

using the sql as it is know I get the wrong month returned for example it returns the month february when the start month =6 and the end month = 9 for that month but wks = 35.

[/QUOTE]

 Set RstWk = Dbs.OpenRecordset("select * from weeks where Val([month start]) >= '" & wks & "' and val([month end]) <= '" & wks & "'") 'and val([year]) = '" & yrs & "'")

[brucevde]

Couble be a typo, you said all fields/variables are integers (except for Month), but your sql statement is treating them like strings.

Try getting rid of the Val function and the single quotes.

[astralvoid]

Since you seem to be using VB, is there a reason you have to use an SQL statement to obtain this information? The week# of each month can vary slightly from year to year so a fixed table to hold this information would need to be updated yearly.

One VB method to update this table would be:



'currentDate = the date you are searching to the week#
'beginDate = Jan 1st of the year in wich the currentDate falls

private Function ConvertDateToWeek(byVal currentDate as Date, byVal beginDate as Date) as Int

Dim week as integer

week = Datediff("ww", beginDate, currentDate)

return week

end Function



Hope this helps!!

[JFK1965]

brucevde & astralvoid thanks for the interest

I'm using VBA in access97 but I've been requested to provide a sales report that shows the customers sales orders per month for the last 6 months and the next 5 months.

The problem is I've inherited this database and the date info is stored in a text field in the format ww/yy so I thought about using the fixed table showing what weeks make up each month, I know that the table will need continous updating but at the moment I can't see a clear way around this.

So far I've retrieved the sales orders for the previous 6 months ok but I'm struggling with this part identifying which week falls in which month.

Any ideas much appreciated

thanks

JFK

[astralvoid]

The same formula should work in VBA.

Create a form that prompts you for the currentDate and beginDate, then executes the function and calls your VBA code to fill in the Table.

[edit]

Call the function from a loop that would input the current date as a list of the months you need calculated. (even daily if need be for a total of 365 rows)

eg. 02/01/2003, 02/28/2003, 03/01/2003.....etc..

Once you have this table filled with the week <==> date information, you could execute a query to compare your ww/yy field with the week field in your new table/query