Queries based on a date field

[June7]

Did you read post 12?

If you need to know if any records in the dataset have expired, then have a textbox on form header section with expression:

="Vaccinations are due for " & Sum(IIf([rabiesduedate] < Date - 30,1,0)) & " records."

Then if you want to throw something in user's face when the form opens:

Private Sub Form_Load()
MsgBox Me.tbxCount
End Sub

This does require the form RecordSource include rabiesduedate field.

[mstrbfg]

the table is the record source not sure about being bound, how can i check?

[aytee111]

In form design, the field that is the rabiedatedue, its control source should have the corresponding name from the table. There is a down arrow on it and you select a field from the record source. In general practice, it is best if the name of the text box matches the name on the table/control source for bound fields. If it is an unbound control then the control source will be blank or will contain an expression (beginning with =).

[Micron]

In general practice, it is best if the name of the text box matches the name on the table/control source for bound fields.

If by this you mean that a textbox whose source is a table or query field (e.g. LName field) should have the same name as that field, I don't think I could disagree more.

Any information I've ever read about general naming conventions precludes this (e.g. naming the control txtLName). Besides, in my experience, once you've debugged a code problem where it turns out that Access didn't distinguish as intended between a control with the same name as its bound field, you probably would avoid that practice. At least that's how I roll.

If on the other hand you meant the textbox shouldn't be named Text12 (the typical default of a form built without using the form wizard), then I guess we'd agree.

[June7]

I'm with Micron. I give controls a name different from the field. If the field is Quantity then I name the textbox tbxQty.

[mstrbfg]

I still cant get this to work. I want it to pop up a message that vaccinations are due 30 day before. any assistance is appreciated

[klnlsu]

You want the dates to fill? What dates?
The query would pull the dates from the form
TxtStartDate on the form. (Unbound)
txtEndDate source= DateAdd("d",30,txtStartDate)

the query would use:
select * from table where date between forms!myForm!txtStartDate and forms!myForm!txtEndDate

I am having a similar problem with my query not reading my date fields. I have a query that reads its criteria from an open form. The query reads textboxes on the form that contain alpha or numeric data correctly, but the textboxes that contain dates show as NULL in my query. Can someone tell me why?

[June7]

I have concerns about data structure. I will assume each dog can have only one owner but each owner can have multiple dogs. Therefore OwnerID should be saved into Dogs table, not DogID into Owners table.

Why isn't RabiesDueDate in the Dogs table? Owners don't get rabies shot, dogs get shot.

Advise no spaces or punctuation/special characters (underscore only exception) in naming convention.

Some dummy records already entered would have been helpful.

If you want to know what records have a due date between today and the next 30 days, try: WHERE (((Owners.[Rabies Due Date]) Between Date() And Date()+30));

klnlsu, you should start your own thread for your issue instead of hijacking this one, you may refer to existing thread if you think that will help readers understand issue. And no, I have no idea why that is happening. You have not provided anything for us to analyze.