Extract Year From Control On Form (1 Viewer)

phillip

New member
Local time
Today, 08:17
Joined
Aug 21, 2025
Messages
14
Need a little help.

I have a form named BankTransactions that has a field named ComboYear. ComboYear stores the year the user enters (such as 2025).

I am trying to build a query that strings together the date range "1/1" and then adds in the year from ComboYear from the form BankTransactions.

Not sure how to build the query correctly so that it pulls everything on 1/1/2025.
 
What will be the origin of your "1/1" and how do you propose to get to 1/2 (or 2/1, depending on whether you were using USA or British date format standards)?

The next question is "what is the datatype for the year in that control?"

With that information, we could tell you how to string that together. HOWEVER, what is the REAL purpose here? If you are generating a "calendar" for which some things might be blank i.e. no entry on that date, this might not be the optimal way to do it.

Just so you don't think I'm passing you off, look up "concatenation" which, for strings, is the way to build longer strings by combining shorter components. However, remember also that things in STRING or Text formats are generally not usable in computations, and because there are multiple date formats in use in Access (and Windows), text comparisons can give questionable results.
 
What will be the origin of your "1/1" and how do you propose to get to 1/2 (or 2/1, depending on whether you were using USA or British date format standards)?

The next question is "what is the datatype for the year in that control?"

With that information, we could tell you how to string that together. HOWEVER, what is the REAL purpose here? If you are generating a "calendar" for which some things might be blank i.e. no entry on that date, this might not be the optimal way to do it.

Just so you don't think I'm passing you off, look up "concatenation" which, for strings, is the way to build longer strings by combining shorter components. However, remember also that things in STRING or Text formats are generally not usable in computations, and because there are multiple date formats in use in Access (and Windows), text comparisons can give questionable results.
Thanks for your input. This is the code I was looking for: "1/1/" & [Forms]![BankTransactions ]![ComboYear]. This returns all records with a date of 1/1/2025.
 
Thanks for your input. This is the code I was looking for: "1/1/" & [Forms]![BankTransactions ]![ComboYear]. This returns all records with a date of 1/1/2025.
If you are using this in a query on a date field, DateSerial is a better choice because it returns a variant date instead of a string.

Code:
DateSerial([Forms]![BankTransactions ]![ComboYear],"1","1")
 
If you are using this in a query on a date field, DateSerial is a better choice because it returns a variant date instead of a string.

Code:
DateSerial([Forms]![BankTransactions ]![ComboYear],"1","1")
Why the extra quotes? Works fine without them?

Code:
tt=2025
? dateserial(tt,1,1)
01/01/2025 
tt="2025"
? dateserial(tt,1,1)
01/01/2025 
? dateserial("2025","1","1")
01/01/2025
 
If you are using this in a query on a date field, DateSerial is a better choice because it returns a variant date instead of a string.

Code:
DateSerial([Forms]![BankTransactions ]![ComboYear],"1","1")
Don’t use strings in DateSerial
Code:
DateSerial([Forms]![BankTransactions ]![ComboYear],1,1)
 
you can use DateSerial(),
you can also use CDate() function:

Code:
CDate([Forms]![BankTransactions]![ComboYear] & "-1-1")
 

Users who are viewing this thread

Back
Top Bottom