Dates issue (1 Viewer)

[liverpoolphil]

Hi all, first post on here hope I can get some help with an issue I've been trying to sort out (and failed) I'm no access expert but cant seem to find a solution to something I think some of you may be able to solve easily

I want to create a query which searches column 4 for dates within a specific date range (I've managed to sort this bit out) but NOT INCLUDE dates in the future (this I cant get to the bottom of

Any help would be greatly appreciated

 

[bob fitz]

What expression are you using for the criteria of column 4

 

[liverpoolphil]

thanks for your response bob fitz - Column's 1-4 are all date/time data types - this the information you're looking for?

 

[theDBguy]

Hi. Welcome to AWF!

Is that an image of your table or is it a crosstab query? What does each date represent? What does each column of dates represent?

 

[bob fitz]

thanks for your response bob fitz - Column's 1-4 are all date/time data types - this the information you're looking for

No. you said

I want to create a query which searches column 4 for dates within a specific date range (I've managed to sort this bit out) but...

so I assumed that what you were showing us was the result of a query and I wanted to know what criteria you had applied to the query.
If what you showed is not a query then please explain how you are searching for a specific date range.

 

[Pat Hartman]

Assuming the column is defined as a datetime data type,
the criteria would be something like:

Where fld4 Between Format(Forms!yourform!txtStartDT, "yyyy/mm/dd") and Format(Forms!yourform!txtEndDT, "yyyy/mm/dd")

The reason for formatting the dates is because internally SQL Server assumes US date format of mm/dd/yyyy and so will not return the desired results if your native format is dd/mm/yyyy. Converting to the unambiguous yyyy/mm/dd is the easiest solution.

 

[The_Doc_Man]

Normally, these columns are NAMED because they are fields. To call them columns immediately betrays Excel thinking. Which is great when you are working in Excel; not so much when in Access. Further, that display SEEMS to be an Excel worksheet, but on second glance I see that perhaps what you have done is created tables for which the fields are named "1", "2", etc. This is not generally a good idea because somewhere along the way, you are going to forget the bracketing and that number will be treated as some abstract constant. Let's say you renamed the columns to DateX, DateY, DateZ, and DateW just to avoid the overlap (though those are lousy if the dates have some specific meaning that is different for each field.)

While I'm at it, DAY (as a field name) is also unwise. It isn't a reserved word, but it IS the name of a VBA function used in picking apart dates. So it is also a bad choice for a field name.

If you wanted to search the DateW field for specific date ranges AND the dates must not be in the future, it MIGHT look like this:

SELECT DateX, DateY, DateZ, DateW, Day
FROM sometable
WHERE
    ( DateW < Date() ) AND
    ( DateW >= startdate ) AND
    ( DateW <= enddate ) ;

I should point out that this is NOT a complete answer because the context of how you create the SQL statement defines how you would provide the starting and ending dates. I also didn't know your table name.

 

[liverpoolphil]

we sell our service in block of 4 (appointments) so each date represents an appointment. I would like to search the table for those who have completed their block of 4 (i.e the date in column 4 is less than today) but also to exclude people who have already paid (hence the date in their 4th column would be after today - hope that makes sense

 

[Pat Hartman]

So just look for records where the fourth date is < Date()

 

[liverpoolphil]

Hi. Welcome to AWF!

Is that an image of your table or is it a crosstab query? What does each date represent? What does each column of dates represent?

that's an image of my table, the dates are for appointments which are booked in blocks of 4 and I'm trying to interrogate the table to show me those Session 4's between a certain date but not beyond todays date (i.e. those who are finishing their block of 4 within a date range but haven't paid for their next block)

 

[liverpoolphil]

So just look for records where the fourth date is < Date()

yes BUT also not greater than today

 

[liverpoolphil]

No. you said

so I assumed that what you were showing us was the result of a query and I wanted to know what criteria you had applied to the query.
If what you showed is not a query then please explain how you are searching for a specific date range.

The image is a picture from a table ... I am (trying) to use a query to interrogate that table show show me those column 4's between a certain date but not beyond today

 

[theDBguy]

that's an image of my table, the dates are for appointments which are booked in blocks of 4 and I'm trying to interrogate the table to show me those Session 4's between a certain date but not beyond todays date (i.e. those who are finishing their block of 4 within a date range but haven't paid for their next block)

Are your fields actually named 1, 2, 3, 4, and Day? That makes it look like you don't have a normalized table structure.

In any case, to better understand your requirement, can you present two sets of sample data? One to show what you have, and the other to show what you want to get out of it. For example, if we use the sample image you posted above, which rows did you want to get back from it?

 

[liverpoolphil]

Are your fields actually named 1, 2, 3, 4, and Day? That makes it look like you don't have a normalized table structure.

In any case, to better understand your requirement, can you present two sets of sample data? One to show what you have, and the other to show what you want to get out of it. For example, if we use the sample image you posted above, which rows did you want to get back from it?

Yes the fields are named 1,2,3,4 & Day
In the image I posted....if I was to use my current query to search for 4's between 20/4 and 25/4 this customer would appear in the result BUT this customer has paid for a further block of 4 sessions (up to 23/5) so I don't want them to appear in my "list of customers due to finished their block of 4 with the dates specified"

 

[Pat Hartman]

yes BUT also not greater than today

If a date is < today, it cannot at the same time be > today.

 

[liverpoolphil]

I'm looking to identify those dates in column "4" that are within a set range but don't have a date greater than today in the cell(?) underneath, hope that makes sense and thanks for your input

 

[Pat Hartman]

Relational databases are not spreadsheets. There is no such thing as a cell or the concept of "underneath".

If you have a unique identifier and you can sort on that, you can use it in a self join. So you use what is called a non-equi join. Add two copies of the table to the QBE. Access will suffix the second copy with "_1" to ensure the names are unique. Select the fields you want from both tables. Start with the normal inner join. Then switch to SQL view and change the "=" in the join to "<=". This will prevent you from going back to QBE mode so make sure you've got what you need before you do this or are prepared to work with SQL directly. Then add a where clause

Where Yourtable.YourDate >= Yourtable_1.YourDate;

This is air code and I don't have data to work with so if this doesn't work, try changing the "<=" in the join to ">=" and then swap the ">=" in the Where to "<=". Some variation of this will give you what you want. Try various combinations before giving up.

HOWEVER, it won't work unless you have a UNIQUE field to sort on since that is the only way you can put the recordset into a fixed, predictable order.

 

[Gasman]

Yes the fields are named 1,2,3,4 & Day
In the image I posted....if I was to use my current query to search for 4's between 20/4 and 25/4 this customer would appear in the result BUT this customer has paid for a further block of 4 sessions (up to 23/5) so I don't want them to appear in my "list of customers due to finished their block of 4 with the dates specified"

So I would expect you would need to identify the max date for each customer in a sub query, and then refer to that with NOT IN () ?

 

[Pat Hartman]

BTW, no one has mentioned this yet but using repeating groups like this (four dates) locks you unnecessarily into a structure that could/should be more flexible. It would probably be better to normalize the table so that it has a child table with one row for each session. That way you could sell 3 or 12 or whatever you wanted. It already seems to be problematic for you since you do sell additional "blocks"