Criteria Using Dates (1 Viewer)

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
Hello,

I am fairly new to Access. I got the basics down, but I am not too familiar with SQL and VBA.

I am trying to build a query that returns back records based on a Date field. I made a "Search Form" for people to input information such as:

(all of the following are unbound)
Employee (combo box)
Team (combo box)
Date (text box short date)
Week of (text box short date)

I got the Employee and Team criteria to work, but the next part is tricky. I want the query to search [Date] if it is not blank, and [Week of] if it is. I want the [Date] to return just that day for the employee or team. For week of, I want to return the date input into Week of and the 6 days following that.

I tried this criteria: (DateKey is the name of the field on the table i am trying to query)

IIf([Forms]![SearchForm]![Date] Is Not Null,[DateKey]=[Forms]![SearchForm]![Date],[DateKey] Between [Forms]![SearchForm]![Week] And ([Forms]![ShiftSearchForm]![Week]+6))

It came up with 0 results when i entered a date into the Date text box.

Any help would be greatly appreciated!
 

Ranman256

Well-known member
Local time
Today, 14:13
Joined
Apr 9, 2015
Messages
4,339
make a button that will filter the results of your combo boxes. (continuous form of all records)
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.
Code:
if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

if IsNull(cboState) then
  me.filterOn = false
else
       'remove 1st And
    sWhere= mid(sWhere,4)

     'just use the filter

  me.filter = sWhere
  me.filterOn = true
endif
 

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
I don't think that is what I am looking for. Im having more trouble with the criteria where it takes the date and shows records for that day plus 6 more days.

I used Like "*" & [Employee] & "*" for my employee and team combo boxes to account for empty boxes.
 

sneuberg

AWF VIP
Local time
Today, 11:13
Joined
Oct 17, 2014
Messages
3,506
I suggest trying the DateAdd function rather than just adding 6 and see if that helps i.e., change

Code:
([Forms]![ShiftSearchForm]![Week]+6)

to
Code:
(DateAdd("d",6,[Forms]![ShiftSearchForm]![Week]))
 

sneuberg

AWF VIP
Local time
Today, 11:13
Joined
Oct 17, 2014
Messages
3,506
On the other hand it should product a result if Date is not null no matter how you add to Week. Could you upload your database.
 

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
On the other hand it should product a result if Date is not null no matter how you add to Week. Could you

Unfortunately not, as the database contains sensitive information.

This is my formula now, but it still doesnt work:

If([Forms]![ShiftSearchForm]![Date] Is Not Null,[Forms]![ShiftSearchForm]![Date],([Shift].[DateKey]) Between [forms]![ShiftSearchForm]![Week] And DateAdd("d",6,[Forms]![ShiftSearchForm]![Week]))

This is messing up the entire query. Anytime I tried to put a criteria for DateKey in here, it shows no result. But when i leave the criteria blank, the Employee and Team searches work.
 

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
Then I suggest uploading a stripped down version[/URL]. In your case I suggest just creating a new database and importing the applicable form, table and query, delete sensitive data from the table and upload that.

I am sorry, but that will prove to be too difficult to work with the way my data already is. (I know, I am not helping much, and I appreciate you trying to help me)

Maybe if I simplify my question:

How do I get records (each including a date) to show the date that is searched and the following 6 days to get a week view?

I was going to present the results in a Multiple Items Form.
 

MarkK

bit cruncher
Local time
Today, 11:13
Joined
Mar 17, 2004
Messages
8,178
Will this work for you?
Code:
SELECT *
FROM YourTable
WHERE DateInTable >= DateToTest AND DateInTable <= DateToTest + 6
 

sneuberg

AWF VIP
Local time
Today, 11:13
Joined
Oct 17, 2014
Messages
3,506
How do I get records (each including a date) to show the date that is searched and the following 6 days to get a week view?

I'd do it the way you are. That's why I'd like to see the database. I don't understand why that's not working.

I could (and have in the past) create a form, table and query based on the information in your post and do some testing but why should I do that when its so much easier for you to do? Of course you can wait. The other forum members may be able to see what's wrong from what you posted.
 

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
So I actually found a workaround that works for what I am trying to accomplish. I made another text box and labeled it Date End and renamed Date as Date Start. My Criteria is now:

Between [Forms]![ShiftSearchForm]![DateStart] And [Forms]![ShiftSearchForm]![DateEnd]

This works perfectly, and if I want to view just one day, I use the same date for start and end.

Thank you for the help sneuberg!
 

aalexander

New member
Local time
Today, 11:13
Joined
Mar 21, 2017
Messages
6
I'd do it the way you are. That's why I'd like to see the database. I don't understand why that's not working.

I could (and have in the past) create a form, table and query based on the information in your post and do some testing but why should I do that when its so much easier for you to do? Of course you can wait. The other forum members may be able to see what's wrong from what you posted.

I completely understand, I was not expecting people to go out of their way to make a test database for a small problem such as this. Thank you for the help, your responses helped me to think about the problem from different angles.
 

Users who are viewing this thread

Top Bottom