Need help creating a date from two fields to be used in where clause

[BugZap13]

I have a table emphours which has two fields, month and year. The month is stored as "January", "February"... The year is stored as a 4 digit numerical value.

I want to use the two fields and make a date from them so I can use it in a where clause of a query similar to below.

where emphours.month+emphours.year between dBegDate and dEndDate

What is an easy way to convert the two fields into a date so I can use it in the query?

TIA, Mark

 

[gmmastros]

You'll need to be very careful here. Storing a month as the name ("January", "February") can be problematic because of typos and whatnot.

Regardless, since you want to compare these things as dates (as you should), you will need to also have a "day" component, specifically, which day of the month.

Maybe try this...

where Convert(DateTime, emphours.month + ' 01, ' + Convert(VarChar(4), emphours.year) between dBegDate and dEndDate

also.. please note that since you are essentially creating a date value on the fly based on data in 2 separate columns, you will likely not be able to use an index. Basically, this means that your query will likely be slower than it could be.

 

[BugZap13]

I agree with all you say regarding the split date in the database. Fortunately, the month is derived from a pulldown on a form. Your comment regarding the index makes sense and I do have more code in the where clause that will limit the number of records I am processing that will be Rushmore optimized.

SELECT EmpHours.Hours 
FROM [Hours table] EmpHours 
WHERE CONVERT(DateTime, EmpHours.Month, 01, Str(EmpHours.Year)) BETWEEN #11-1-2023# AND #10-31-2024#

When I run the above I am getting an error as follows:

Undefined function 'CONVERT' in expression.

I have used "CONVERT()" in tsql but it appears it is not valid for access.

 

[Andrzejek]

Use this code as a hint of one way to convert your Month and Year into a date:

Dim strMonth As String
Dim intYear As Integer
Dim datMyDate As Date

strMonth = "June"
intYear = 2023

datMyDate = CDate("01/" & strMonth & "/" & intYear)
Debug.Print datMyDate

 

[mikrom]

Try to create two functions:

1) yyyymm (year, month)
which for integer year and month as strings returns integer value yyyymm,
for example:
yyyymm(2024, "January") = 202401
yyyymm(2024, "February") = 202402

2) yyyymm_from_date(date)
which from date in your format returns integer value yyyymm,
for example:
yyyymm_from_date(#11-1-2023#) = 202311
yyyymm_from_date(#10-31-2024#) = 202410

then you will be able to use the functions in a where clause of a query like this:

  ...
  where
    yyyymm(emphours.year, emphours.month)
    between yyyymm_from_date(dBegDate) and yyyymm_from_date(dEndDate)