date from form

  • Thread starter Thread starter Dale and Sandi Brown
  • Start date Start date
D

Dale and Sandi Brown

I have a form with a text box that has a date in it. When I try to have a
query access this box it does not work. Not sure what is wrong. The where
clause in the query is as follows:

ebreceived.insertdate >= [forms]![checkregister]![bdate]

Any ideas of what could be wrong?
 
Dale and Sandi Brown said:
I have a form with a text box that has a date in it. When I try to have a
query access this box it does not work. Not sure what is wrong. The where
clause in the query is as follows:

ebreceived.insertdate >= [forms]![checkregister]![bdate]

Any ideas of what could be wrong?
Click to expand...

What do you mean by "does not work"? Do you get an error? If so, what is
it?
 
No I do not get an error. I just get back 0 records when I know I should
be getting back records.
I have a form with a text box that has a date in it. When I try to have a
query access this box it does not work. Not sure what is wrong. The where
clause in the query is as follows:

ebreceived.insertdate >= [forms]![checkregister]![bdate]

Any ideas of what could be wrong?
Click to expand...


What do you mean by "does not work"? Do you get an error? If so, what is
it?
Click to expand...
 
No I do not get an error. I just get back 0 records when I know I should
be getting back records.
I have a form with a text box that has a date in it. When I try to have a
query access this box it does not work. Not sure what is wrong. The where
clause in the query is as follows:

ebreceived.insertdate >= [forms]![checkregister]![bdate]

Any ideas of what could be wrong?
Click to expand...


What do you mean by "does not work"? Do you get an error? If so, what is
it?
Click to expand...
Click to expand...

You haven't given much to go on, it could be all manner of things. Are you
sure that [forms]![checkregister]![bdate] actually has a date value in it
when the query runs? What data type is insertdate? Are you sure it's a
date? What happens if you remove this criterion? Do you still get zero
rows? If so, then something is wrong somewhere else. If you DO get rows
without this criterion, then add the form's field to the query as a field,
and add insertdate to the query as a field, so you can actually see and
compare the returned values, which should make it clear what the problem is.

Failing all that, you will need to post the SQL for the query, and a lot
more information about it!
 
Baz,

dateinserted is a date/time field in the table ebrecieved. Below I have
copied in the query.

SELECT EB.EBNumber, Person.FirstName & " " & Person.LastName AS Owner,
Person.Address & " " & Person.City & ", " & Person.State & " " &
Person.Zip AS csz, MoneyType.MoneyDesc, EBReceived.Amount,
EBReceived.Note, ebreceived.dateinserted,
ebreceived.updatedate,[forms]![checkregister]![bdate]
FROM MoneyType INNER JOIN (Person INNER JOIN (ActiveShow INNER JOIN (EB
INNER JOIN EBReceived ON EB.EntryBlankID = EBReceived.EntryBlankID) ON
ActiveShow.ShowID = EB.ShowID) ON Person.ID = EB.OwnerID) ON
MoneyType.MoneyID = EBReceived.MoneyID
WHERE format(ebreceived.dateinserted,'mm/dd/yyyy') > '' or
format(ebreceived.updatedate,'mm/dd/yyyy') > ''
and (format(ebreceived.dateinserted,'mm/dd/yyyy') =
[forms]![checkregister]![bdate] or
format(ebreceived.updatedate,'mm/dd/yyyy') =
[forms]![checkregister]![bdate] );

if I remove the where format...... I get the records that I should get.
When I add in the clause and the field [forms]![checkregister]![bdate] I
do not get what I think I should and the field returns nothing. I ran
this query without actually having the form up and inputted the date
when prompted and still got nothing in the field. Any ideas?
No I do not get an error. I just get back 0 records when I know I should
be getting back records.
I have a form with a text box that has a date in it. When I try to have
Click to expand...
a
query access this box it does not work. Not sure what is wrong. The
Click to expand...
where
clause in the query is as follows:

ebreceived.insertdate >= [forms]![checkregister]![bdate]

Any ideas of what could be wrong?




What do you mean by "does not work"? Do you get an error? If so, what
Click to expand...
is
it?
Click to expand...
Click to expand...

You haven't given much to go on, it could be all manner of things. Are you
sure that [forms]![checkregister]![bdate] actually has a date value in it
when the query runs? What data type is insertdate? Are you sure it's a
date? What happens if you remove this criterion? Do you still get zero
rows? If so, then something is wrong somewhere else. If you DO get rows
without this criterion, then add the form's field to the query as a field,
and add insertdate to the query as a field, so you can actually see and
compare the returned values, which should make it clear what the problem is.

Failing all that, you will need to post the SQL for the query, and a lot
more information about it!
Click to expand...
 
Baz,

dateinserted is a date/time field in the table ebrecieved. Below I have
copied in the query.

SELECT EB.EBNumber, Person.FirstName & " " & Person.LastName AS Owner,
Person.Address & " " & Person.City & ", " & Person.State & " " &
Person.Zip AS csz, MoneyType.MoneyDesc, EBReceived.Amount,
EBReceived.Note, ebreceived.dateinserted,
ebreceived.updatedate,[forms]![checkregister]![bdate]
FROM MoneyType INNER JOIN (Person INNER JOIN (ActiveShow INNER JOIN (EB
INNER JOIN EBReceived ON EB.EntryBlankID = EBReceived.EntryBlankID) ON
ActiveShow.ShowID = EB.ShowID) ON Person.ID = EB.OwnerID) ON
MoneyType.MoneyID = EBReceived.MoneyID
WHERE format(ebreceived.dateinserted,'mm/dd/yyyy') > '' or
format(ebreceived.updatedate,'mm/dd/yyyy') > ''
and (format(ebreceived.dateinserted,'mm/dd/yyyy') =
[forms]![checkregister]![bdate] or
format(ebreceived.updatedate,'mm/dd/yyyy') =
[forms]![checkregister]![bdate] );

if I remove the where format...... I get the records that I should get.
When I add in the clause and the field [forms]![checkregister]![bdate] I
do not get what I think I should and the field returns nothing. I ran
this query without actually having the form up and inputted the date
when prompted and still got nothing in the field. Any ideas?
Click to expand...

Yes, I can see exactly what's wrong. By applying the Format function to all
those dates, you are converting them to text, and comparing a text value to
a date value will never work. I imagine that what you were trying to do was
this:

WHERE ebreceived.dateinserted IS NOT NULL or
ebreceived.updatedate IS NOT NULL
and (ebreceived.dateinserted = [forms]![checkregister]![bdate] or
ebreceived.updatedate = [forms]![checkregister]![bdate]);

However, the tests for null are redundant, because a Null value can never be
equal to anything anyway, so probably what you really want is simply this:

WHERE ebreceived.dateinserted = [forms]![checkregister]![bdate] or
ebreceived.updatedate = [forms]![checkregister]![bdate]
 
Back
Top