How to combine lookup fields into a date field in MS Access

You can use the following syntax CDate([Start Month]+'/' + [Start Day] + '/' + [Start Year])

@Maria

Thanks for the reply.  I am not an expert in Databases, I merely use them.  So I am somewhat at a loss as to where to use that. For instance, do I make a new field in the table that has the year, month, and day lookup named something like startdate and then what?  or do I use a query to query those fields in the lookup tables (where the ID for January would be 1, and the ID for the first year would be 1), with a relationship to the ID in the main table?  Or maybe in a query, or a form or a report?

Right now I have a report based on a query that used to ask for the start date and the end date using the code in my OP but now asks for a start year (it needs to be the ID number) and an end year (also the ID number).  This is not intuitive at all and I know my users are going to complain.

As you can tell I am not even close to an expert when it comes to databases.

@Maria

Thanks for the reply.  I am not an expert in Databases, I merely use them.  So I am somewhat at a loss as to where to use that. For instance, do I make a new field in the table that has the year, month, and day lookup named something like startdate and then what?  or do I use a query to query those fields in the lookup tables (where the ID for January would be 1, and the ID for the first year would be 1), with a relationship to the ID in the main table?  Or maybe in a query, or a form or a report?

As you can tell I am not even close to an expert when it comes to databases.

I am unclear at this point about what you want to do.  You say you have code that asks for the start year and end year. Do you want to leave that in?  Are you asking how to convert the returned start year to a start date?

I apologize, I was probably unclear.  I have a query that asks for the start and end date and returns a report that lists everyone in the database that has that information in their record.  The problem is that I have the information for them in the month, day, and year lookup fields.  For instance, John Smith started on 2/3/2022 so his information on his start date is an id lookup to the month of February (ID = 2), Day lookup to the day 03 (ID=3), and a year lookup to the year 2022 (ID=67).  My original code works easily on a date field (02/03/2022) but not on the separate lookup fields.  So with the code in my OP I can ask the end-user for an exact date for start and end, but with the data in my database most entries only have the information from the lookup fields (out of ~360 records about 16 have an entry in the start date field and one has an entry in the end date field).  What I would like to do is have all the information be able to be queried so I can have the user generate a report by start and end dates without having to know the exact dates and preferable having a dropdown list to choose from.

Something like "show me everyone who started between 2/1/2019 and 1/1/2021".  The code in the original post does that but only queries the records where the information has been entered.  Since I have the information in other fields in other formats, is there any way to query those lookup fields (StartYear_ID, StartMonth_ID, StartDay_ID, and the same with the End dates) in combination instead?

so, you would create a query which contains your main table joined to each of the Year, Month and day tables on the appropriate fields.  
Then you could use the DateSerial([Year], [Month], [Day]) function to generate the StartDate.

Thanks Dale - I'll give it a try.

@Dale,

I tried your solution, I think, but either I'm not really good at this or something else is getting in the way.   The various tables (year, month,  day)  point to 6 different lookups in my main table (tbldemog),  start year, start day, start month, end year, end month, and end day.

When I set up a simple query,  I get each record in the database repeating multiple times. each record does have a lookup field (e.g.,tblStartDay_ID), but that doesn't seem to help.

What am I doing wrong?

@Thomas please share your query

I'll upload it from work on Wednesday AM (SQL?)

Yes if you could switch into SQL view and then copy and paste your query here that would be helpful

This is with the start day.month and year related to the main table (tbldemog.startday_id, etc).  This query returns NO records

SELECT tblDemog.ID, tblDemog.LastName, tblDemog.FirstName, tblDemog.[Mid Initial], tblDemog.[Date Added], tblDemog.tblDept_ID, tblDemog.tblStatus_ID, tblDemog.tblPrgStatus_ID, tblProg.NewProgramAbbreviation
FROM tblYear INNER JOIN (tblMonths INNER JOIN (tblDays INNER JOIN (tblProg INNER JOIN tblDemog ON tblProg.ID = tblDemog.tblProg_ID) ON tblDays.datesID = tblDemog.startDay_ID) ON tblMonths.ID = tblDemog.startMonth_ID) ON tblYear.ID = tblDemog.startYear_ID
WHERE (((tblDemog.[Date Added])>=[Start Date:] And (tblDemog.[Date Added])<=[End Date:]));

This is with the tables added but not related, and returns multiple records per person/record

SELECT tblDemog.ID, tblDemog.LastName, tblDemog.FirstName, tblDemog.[Mid Initial], tblDemog.[Date Added], tblDemog.tblDept_ID, tblDemog.tblStatus_ID, tblDemog.tblPrgStatus_ID, tblProg.NewProgramAbbreviation
FROM tblDays, tblMonths, tblYear, tblProg INNER JOIN tblDemog ON tblProg.ID = tblDemog.tblProg_ID
WHERE (((tblDemog.[Date Added])>=[Start Date:] And (tblDemog.[Date Added])<=[End Date:]));

This is with nothing added and returns the correct records.
SELECT tblDemog.ID, tblDemog.LastName, tblDemog.FirstName, tblDemog.[Mid Initial], tblDemog.[Date Added], tblDemog.tblDept_ID, tblDemog.tblStatus_ID, tblDemog.tblPrgStatus_ID, tblProg.NewProgramAbbreviation
FROM tblProg INNER JOIN tblDemog ON tblProg.ID = tblDemog.tblProg_ID
WHERE (((tblDemog.[Date Added])>=[Start Date:] And (tblDemog.[Date Added])<=[End Date:]));

I created this using the simple query wizard.

I think perhaps you just have your join order incorrect.  Try
SELECT tblDemog.ID, tblDemog.LastName, tblDemog.FirstName, tblDemog.[Mid Initial], tblDemog.[Date Added], tblDemog.tblDept_ID, tblDemog.tblStatus_ID, tblDemog.tblPrgStatus_ID, tblProg.NewProgramAbbreviation
FROM (((tblProg
INNER JOIN tblDemog ON tblProg.ID = tblDemog.tblProg_ID)
INNER JOIN tblYear ON tblYear.ID = tblDemog.startYear_ID)
INNER JOIN tblMonths ON tblMonths.ID = tblDemog.startMonth_ID)
INNER JOIN tblDays ON tblDays.datesID = tblDemog.startDay_ID
WHERE (((tblDemog.[Date Added])>=[Start Date:] And (tblDemog.[Date Added])<=[End Date:]));

I pasted that code into the SQL view of the query (well a copy) and it returns no records.

Can you provide a better insight as to what your data looks like?  What is the purpose of the tblDemog record?  Does every tblProg have a tblDemog record?  What does that data in tblDemog look like.  You say that not all records have Date Added filled in, do all records on tblDemog have startDay_ID, startMonth_ID, startYear_ID filled in?  What do those tables look like?  A couple of sample and the key fields in them would be helpful.  You may be able to swap out the
WHERE (((tblDemog.[Date Added])>=[Start Date:] And (tblDemog.[Date Added])<=[End Date:]));
for some WHERE clause that uses values directly from those tables (or a concatenated field to compare to)

I'll upload some samples tomorrow AM.

I realize that I am unsure of how to upload samples.  Suffice it to say that TblDemog is too big and has too many lookups.  To answer your question on whether all records have the start/end date information filled in - the answer is yes, with the caveat that it is not in date format.  Also since I created this database quite a long time ago, some of the start dates are "<prior to 1992>".

I am a little reticent to post a database table I created that is most likely something so unwieldy that anyone with DB experience would scoff at it (I am in no way a DB "expert"). There is a sample below (I finally just figured I'd export to excel in order to upload).

On a completely other note, but still on MS Access, I just ran into a problem that I am unsure of how to resolve.  I want to display on a form the old role and the new role.  I had added the new roles to my old role table (Program Roles), but I then copied that table (data and structure) to a new table called Program Roles - CCSG.  The Program Roles and Program Roles - CCSG tables have 2 fields in them, the ID field and the Role Name field - Role Name in the second table and Program Role Name in the first table.  When I went to create a lookup from the tblDemog table, the lookup wizard only lists the ID field and not the Role name field.  Any ideas on this one also?


Program Roles - CCSG.xlsx
Program Roles.xlsx
tblDemog.xlsx

Interesting addendum (at least to me), when I added the field "SortOrder" to the "Program Roles - CCSG" table trying to do a lookup wizard shows the ID field and the SortOrder field, but still not the Program roles field.

I don't know why the other tables were acting up, but I created a new table with virtually the same information (I left out some entries I no longer use). It seems to be working now - at least the program roles are working ...