Background info for the database: I am working on a database tracking publications. Years will always be listed for the journal articles, and months should be. Days, on the other hand, are listed for some, but not for others. I kept year, month and day as separate fields because I don't want to add 'day' data if it doesn't actually exist.
I do want to be able to search by dates, though, so I wanted to turn these into full dates where if month or year is missing, a bad date (1/1/1900) prints, and if there is no day listed, it lists it as '1' for searching purposes only.
In a query, I used DateSerial to combine this into a FullDate
FullDate: IIf(IsNull([PubYear]), DateSerial(1900,1,1), IIf(IsNull([PubMonth]), DateSerial(1900,1,1), IIf(IsNull([PubDay]),DateSerial([PubYear],[PubMonth],1), DateSerial([PubYear], [PubMonth],[PubDay]))))
This appeared to have worked, and the dates are listed in the FullDate field formatted as m/d/yyyy.
The problem is when I try to run another query where the user inputs a Report Start Date and Report End Date. Out of habit, I entered the dates m/d/yy. This did not filter correctly. Then I tried m/d/yyyy. This worked if I used equal, but not < or >. Is data from DateSerial actually spitting out dates? or is it just formatting it like a date, but treating it like text? I don't get why I can't filter it. It does something, but it's definitely not correct.
Yes, there are probably always better ways using VBA, but my coding is super rusty.
I tried to do screenshots and/or links, but my post count isn't high enough. Had to remove them.
I do want to be able to search by dates, though, so I wanted to turn these into full dates where if month or year is missing, a bad date (1/1/1900) prints, and if there is no day listed, it lists it as '1' for searching purposes only.
In a query, I used DateSerial to combine this into a FullDate
FullDate: IIf(IsNull([PubYear]), DateSerial(1900,1,1), IIf(IsNull([PubMonth]), DateSerial(1900,1,1), IIf(IsNull([PubDay]),DateSerial([PubYear],[PubMonth],1), DateSerial([PubYear], [PubMonth],[PubDay]))))
This appeared to have worked, and the dates are listed in the FullDate field formatted as m/d/yyyy.
The problem is when I try to run another query where the user inputs a Report Start Date and Report End Date. Out of habit, I entered the dates m/d/yy. This did not filter correctly. Then I tried m/d/yyyy. This worked if I used equal, but not < or >. Is data from DateSerial actually spitting out dates? or is it just formatting it like a date, but treating it like text? I don't get why I can't filter it. It does something, but it's definitely not correct.
Yes, there are probably always better ways using VBA, but my coding is super rusty.
I tried to do screenshots and/or links, but my post count isn't high enough. Had to remove them.