Can't filter output dates from DateSerial (1 Viewer)

[aahmn]

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.

 

[CJ_London]

you can simplify your query using nz

FullDate: DateSerial(nz(PubYear,1900),nz(PubMonth,1),nz(pubDay,1))

Date serial returns a date value - with regards the problem with entering two digits, refresh your knowledge of the dateserial function

https://msdn.microsoft.com/en-us/library/bbx05d0c(v=vs.90).aspx?f=255&MSPPError=-2147217396

In particular look at this section
Year Required. Integer expression from 1 through 9999. However, values below this range are also accepted. If Year is 0 through 99, it is interpreted as being between 1930 and 2029, as explained in the "Remarks" section below. If Year is less than 1, it is subtracted from the current year.

 

[aahmn]

I have a habit of making things too complicated. That query makes much more sense.
In regards to the dateserial function, the year number used as an input is always yyyy (PubYear is a full 4 digit year). It's just when I try to query the resulting date that I can't seem to get correct numbers.

 

[aahmn]

Actually, looking at your query, I don't know if that will do what I need. I need it to give a bad date if either month or year are missing, but this way, won't it just look like 1/1/2014 if I had a publication year of 2014 but no month listed?

Here's a screenshot of where I have message boxes that allow the user to enter a start date and end date - in case I'm missing something completely obvious:
https://lh3.googleusercontent.com/-...AAAACo8/OMcXnFOHnUA/s790-Ic42/screenshot2.jpg

Example of how the full data appears without the user start and end dates - FullDate looks okay?
https://lh3.googleusercontent.com/-...AAAACpQ/1EIUiXWR15E/s528-Ic42/Screenshot4.jpg

Example of some of the data when I entered a start date of 1/1/2012 and end date of 12/31/2012 (to view all publications in 2012) - I see that it returns all years, but only the dates that have months with the number 1 in them??
https://lh3.googleusercontent.com/-...AAAACpc/prg1dwbfbOI/s453-Ic42/Screenshot5.jpg

 

[arnelgp]

FullDate: IIf(IsNull([PubYear]), DateSerial(DMin("PubYear","yourTableName","Not IsNull(PubYear)"),1,1), IIf(IsNull([PubMonth]), DMin("PubYear","yourTableName","Not IsNull(PubYear)"),1,1), IIf(IsNull([PubDay]),DateSerial([PubYear],[PubMonth],1), DateSerial([PubYear], [PubMonth],[PubDay]))))

 

[aahmn]

I'm confused as to what Dmin will do for this if I am looking for null values? I'm very rusty at all this.

And I wanted to clarify again, my FullDate result looks right. (?) It looks like a date and the dateserial seemed to work correctly to give the right values. I just can't then further filter those values.

 

[arnelgp]

won't it just look like 1/1/2014 if I had a publication year of 2014 but no month listed?

DMin will look for the smallest year value in your table.

 

[aahmn]

I think I'm just going to suggest to my supervisor that we just purchase EndNote. Spending time trying to reinvent something that is already out there probably isn't the best use of my time.

I still don't know that switching to Dmin will do what I want. I was just hoping for an answer on whether access automatically always treats the output dates from dateserial as actual dates, or if this is something that needs to be specified separately. My outputs, though they looked correct, didn't seem to be searchable as actual dates. Now it's just out of curiosity and maybe for future reference.

 

[Brianwarnock]

I have never had any problems with Dateserial. Are your parameters defined as dates? Have you tried Between.... and ....

Brian

 

[aahmn]

I'm back again about this. It's bothering me that I can't figure out why this didn't work. I tested querying dateserial results with something simple - still can't search it properly. I've attached a super super simple example that does the same thing as my other one did.

The attached database is simply a list of dates where month, day and year are separate numbers. I made a query that used dateserial to combine the numbers into dates. I then created another query that asks for a start and end date (with the fulldate being in between those entries). Again, it spits out bad data. I'm confused... how do I either make sure the results from dateserial are actually searched like dates, or how do I change the formatting?

 

[CJ_London]

dates are a bit special in that they are a number displayed in a date format

Your criteria is returning strings not numbers so you are not comparing like with like.

For a hardcoded string to be treated as a date it has to be surrounded with # e.g.

#10/30/2015#

and the date needs to be the american format of mm/dd/yyyy

alternatively you can use the cdate function to convert a string to a date

so try

>= CDate([StartDate]) And <=CDate([EndDate])

 

[Brianwarnock]

We're you asking for the criteria using text boxes on a form or with pop ups.if the former make sure they are formatted as dates if the later use the parameter keyword to format the pop up.

Specify parameter data types

You can set the parameter to accept only a certain type of data. It is especially important to specify the data type for numeric, currency, or date/time data, because then, people will see a more helpful error message if they enter the wrong type of data, such as entering text when a currency value is expected.

NOTE If a parameter is configured to accept text data, any input is interpreted as text, and no error message is displayed.

To specify the data type for parameters in a query:

With the query open in Design view, on the Design tab, in the Show/Hide group, click Parameters.

In the Query Parameters box, in the Parameter column, enter the prompt for each parameter you want to specify a data type for. Make sure that each parameter matches the prompt that you used in the Criteria row of the query design grid.

In the Data Type column, select the data type for each parameter.

Brian

 

[aahmn]

dates are a bit special in that they are a number displayed in a date format

Your criteria is returning strings not numbers so you are not comparing like with like...

so try

>= CDate([StartDate]) And <=CDate([EndDate])

This is what I needed. I don't know why I got so stuck on it. Thanks!!!

 

[arnelgp]

have you purchased the Endnote?