Query help

[Suff0beast]

Hello all,

New to Access and I am stumped on this simple thing. Using Access 2013

tblPatient
PatientID (PK)
LastName
FirstName
LastFour
TimeIn (uses the Now() value for date/time info)



My query is based on this table alone; I want to be able to pull any new entries that have been added to this table (via frmPatient) each day so I can only update the daily registrations.

qryIntake
PatientID
LastName
LastFour
TimeIn

When I design the query i use the Date() for criteria to only pull those that are on the current day. When I execute this query it does not find any matches. Do the Now() and Date() values don't play nice together?

Basically, everyday I have someone register in to be seen using the frmPatient. Then on the other side I have frmStatus which pulls the query for those who have registered that day. I can then update their status. What I NEED to do is preserve the EXACT time and date that they "signed in" while ONLY being able to pull the same day registrants. Does this make sense?

A good direction would do the trick. If there is a way to log the exact time and date using two different fields I will do that.

Thank you,
R.

[Dal Jeanis]

Now() is a timestamp. You only want the date part of the timestamp for your query.

Internally, a timestamp is stored as the number of days since #12/31/1899#, plus time since midnight. 6:00 AM, for example, is 0.25 days after midnight.

You can use the int() function to get rid of the time portion of the Timein field, and compare against that. Whether you choose to store a DayIN field, or just use int(TimeIn) in all your queries is up to you. Either one is an okay practice.

[Suff0beast]

Outstanding, that did the trick.

Now, it brings up a dialog box for the expression input (which is fine), but with every record update it wants to requery and ask again for the expression. Can I quiet the dialog box that keeps popping up and have it accept the current date?

[Dal Jeanis]

Sure. Just replace the expression term that's being asked for by date(). Or, you can put the current date in a tempvar and use that. Or a half dozen other ways.

Not sure why your query is requerying, though. Is there VBA behind the form that causes a requery on any change?

[Suff0beast]

I'm using the query within a subform to pull the current day registration in so that the tech can update status via PatientID.

When I run the query from the design it does not ask for Intake.TimeIn dialog. When I open the frmStatus Form it presents the dialog box, and then with each submission of the update it will present the dialog box (as if it is requerying). I'll keep messing around with this though. I appreciate your help.

R.

[Dal Jeanis]

Translation: There is a form named frmStatus. The form contains a subform. When the form opens, it pops up a dialog box to ask for Intake.TimeIn? Once you enter something into this dialog, the form acts normally. Whenever the form requeries, it pops up the dialog box again.

If so, this indicates that the form or a subform is based upon a query that improperly includes the term Intake.TimeIn. First, check the record source for the form/subform. If it is a query, close the form and bring up that query in design mode. Switch to SQL mode. Look for the term "Intake.TimeIn"

If it's there, you've found your culprit. Chances are pretty good that field TimeIn has been renamed, or that table Intake has been renamed, or that table Intake has not been properly joined into the query, or that you are not referring to it in a way that allows Access to resolve its value.

If Intake is a table, then check to see the table name and field name are spelled correctly, and the table is included in the FROM clause of the SQL.

If Intake is the name of a form that is open, and TimeIn is a control on the form, then (A) change the SQL in the query to use the fully resolved name of the field - Forms!Intake!TimeIn - and see if that solves the problem. Remember that the form must be open to test this, since the control doesn't exist and has no value unless the form is open.

If Intake is the name of a subform on frmStatus, then you need to find the name of the subform control on frmstatus, and use something like this syntax to refer to TimeIn - Forms!frmStatus.subformcontrolname.Form!TimeIn - and see if that resolves the problem. Remember that the form and subform must be open to test this, since the control doesn't exist and has no value unless the form and subform are open.

[Suff0beast]

You were right! I had to re-evaluate the design of my form. It was pulling from a form of a query of a query (does that make sense?).

Either case I checked the Control Source and set it to the appropriate query. I no longer have the problem with it asking for the information.

Thank you very much

R.

[Dal Jeanis]

Glad to be of help. Sometimes the messages need 'splainin'.