Date Range Question

[JamesJoey]

I want to display only records with a date range from the 1st of the month to the 14th of the month.

Any ideas?
James

 

[isladogs]

What's your date format?

 

[JamesJoey]

I have dated records that start on Nov. 1 and run all month I need to see which record start on Nov. 1 and run to Nov. 14.
Then, I want to see the records that go from Nov. 15 to the last day of the month.


Subsequently, I'll do the same thing for Dec.

 

[isladogs]

Date format is irrelevant. The question is WHAT MONTH? is it always going to be the current month or do you want to specify a month?

Pat,
I disagree. It depends on how the records are obtained.

There are various ways of getting the records for the first 14 days of each month. My first thought for doing so was to either filter the first section (dd/mm/yyyy format) or the middle section (mm/dd/yyyy) format.

If you choose to use DatePart then that's a different matter.

I assumed that James would want this done for each month

 

[isladogs]

If you don't format your dates into strings, you won't ever have trouble with them. Formatting is only for human consumption. Dates internally are stored as double precison numbers and if you work entirely with date objects, you won't have a problem. Only if you have to create an SQL string with an embedded date string will you have to worry about the date format and in that case, it must either be standard US format - mm/dd/yyyy or the more universal and less ambiguous - yyyy/mm/dd

All true - but if you use a query then dd/mm/yyyy can be used if that's the default format as in the UK

For example this query SQL:

SELECT tblPostcodeAddressDates.Postcode, tblPostcodeAddressDates.AddressDownloadDate
FROM tblPostcodeAddressDates
WHERE ((Left([AddressDownloadDate],2)<15 And Mid([AddressDownloadDate],4,2)=11));

returns these records from a table I'm currently working on:

I'm not saying this approach is the best way of doing what James wants.
However, it is one perfectly valid method

Guess what - if you use a formatted string 01/01/2018 is less than 11/11/2017!!! Why? because strings are compared character by character, left to right and 0 is less than 1.

I'm fully aware of that. That's why postcodes starting with AL2 would be sorted after those beginning AL10

JamesJoey tried but he didn't answer my question. The question is - TODAY do you only want to select records between Nov 15 and Nov 30? OR might you want to look at data from OTHER periods?

He didn't answer that question but that wasn't what you asked him!
Perhaps I'm wrong but I can't imagine any reason why you would only want to select data for the first half of the current month.

If TODAY'S date ALWAYS controls what you see, then the query would always use the Date() function to control the selection. If you might want to see other periods, then you need to use a form and have the user pick Year, Month, and period 1 or period 2.

Please try again to answer the question.

I'll leave that for James

 

[arnelgp]

to get the records for the Current Month, 1-14 use Criteria:


[yourDateField] Between DateSerial(Year(Date), Month(Date), 1) And DateSerial(Year(Date), Month(Date), 14)


to get records of Current Month 15-lastday of Current Month:


[yourDateField] Between DateSerial(Year(Date), Month(Date), 15) And DateSerial(Year(Date), Month(Date) + 1, 0)

 

[isladogs]

Using string functions against a date field is your problem.

Pat,
I don't have a problem other than being talked down to...
I gave a perfectly valid solution which I qualified with:

I'm not saying this approach is the best way of doing what James wants. However, it is one perfectly valid method

Arnelgp has just given another more general solution which is equally valid.
James now has a variety of responses to choose from/ adapt.
Personally I have no 'problem' whichever he chooses.

Use date functions. Then you have

WHERE Day([AddressDownloadDate])<15 And Month([AddressDownloadDate])=11; -- see, format is irrelevant

ALWAYS use Date functions to work with dates and format is irrelevant because dates are not stored as strings dates are stored as double precision numbers with the date being the number of days since Dec 30, 1899 (Jet/ACE, the origin for SQL Server and Excel is different) and time being the fraction of a day since midnight as the decimal.

Yes, I'm fully aware of that fact and regularly work with dates in their native double format.

I'm also about to send you a PM

 

[isladogs]

we'll never know unless JamesJoey responds.

Give him time ... he may not spend quite as long on the forum as some of us!