Record search error

[Douglas J. Steele]

Sorry, but that's even more confusing! I thought we were showing you how to
retrieve data, not set values.

What do your tables look like?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, sorry i always have the problem of explaining things poorly.

Ignore, the default value, its my fault.

Right, within this form is a set of data, some fixed and some which is
entered when Agents call a particular client. I then have reports
containing
whether a quote has been sent, rejected or not sent (ie call backs) and I
need a date field to go into only the not sent reports so i can pick up
the
date of the calls.

How do i go about this. THe other two fields, have check boxes that bring
up
the date within the form.

Matt

The form must be open if you're trying to refer to it in the query.

I think you're going to have to step back and explain exactly what it is
you're trying to do. I don't understand what you mean about only wanting
the
ones that have had some form of entry in the form. I also don't
understand
what you mean about default values for current records. Default values
only
apply to new records: once the record's been saved, the field either has
a
value or it doesn't, but the default value no longer applies.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

When it is closed, the parameter appears and even when the form is
open,
the
date does not get updated. I do not awnt all records to have the date
contained. I only want the ones that have been had some form of entry
in
to
the form!

Also, for current records, how do I get the default value to be the
current
user, rather than just for blank records. My form styarts with a blank
recrod
with has the correct default, but then as soon as i select a recrod
this
dissapears. Is it possible?

Matt

:

Make sure you haven't mistyped the name of the form, or of the text
box.

I assume that the form is open when you're running the query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


The problem is that it does not recognise the
Forms!Acceptance!DateToday
and
keeps asking me for a parameter.

The DateToday field is the text box with the default value set to
=Date().
For some reason it wont recognise this.

ANy ideas?

:

Good catch. I would not be surprised.

:

PMFJI. The fact that Matt's commenting about "at this late hour"
implies to
me that he's probably located across the Atlantic from us. That
means
his
Short Date format may be set to something other than mm/dd/yyyy.
If
his
Short Date format is dd/mm/yyyy, that means the SQL is going to
see
Acceptance.[Date] = #04/10/2006#, but Access will treat it as the
10th
of
April, because that's what Access does.

Matt, try:

Acceptance.[Date] =
Format([Forms]![Acceptance]![DateToday],"\#mm\/dd\/yyyy\#")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I put this in the Criteria row of the query builder in the
holdate
column
and
it worked fine
[forms]![zjunk]![txtADate]


:

Have pretty much tried everything around that and I cant get
it
to
work.

I called the text box Date Today and tried both formulas and
to
no
avail.

In the SQL it read: Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date
Today] & "#") and then i tried without the #.

Cheers

Matt


:

Try it without the #
Acceptance.[Date] = ([Forms]![Acceptance]![Date])

Date is not a good name for a field or a control since it is
an
Access
reserved word. The brackets should remove any confusion,
but I
don't
know
that even that is 100% reliable.

:

It doesnt really like the date formula:

In SQL i have entered Acceptance.[Date] = ("#" &
[Forms]![Acceptance]![Date]
& "#").

The first acceptance is the name of the table and the
second
is
the
form!

Matt

:

Now we are talking about two different things. I don't
understand
how the
RequestID you are looking up and how the Date relate.

The date issue is pretty straight foward. If you are
going
to
always use
the current date, it can be in the query. If the user
can
change
the date,
the control they put it in does not have to be bound.
When
you
reference the
value of a control on a form in a query, the query
doesn't
know and
doesn't
care whether it is bound. It only looks at the value.
When
referecing a
form control in a query, regardless of where the control
is
in
the
query, it
is simply
[Forms]![FormName]![ControName]

There are a couple of issues surrounding dates in
queries.
You may
need to
use
"#" & [Forms]![FormName]![ControName] & "#" to tell the
query
it is
a date.
Also, if you are doing something like
[SomeTableDateField] = "#" &
[Forms]![FormName]![ControName] &
"#"
and the
date in the table was populated using Now(), which
contains
time as
well as
date, and comparing it to a date only Date() value, it
will
almost
never
match, because the time portions will be different.

:

Its been a long day!

WOrrying how I can not suss that myself even at this
late
hour!

Did you understand what i meant about the date. I need
current
date in the
form which then updates the relevant query and then in
turn
the
relevant
report. I can have a field which brings up the current
date
but i
can't then
get this into the relevant query. If i make it bound
it
obviously
comes up
blank as the record has a blank date.

Matt

:

It does the type ahead on the combo's row source. A
combo
has
to have a row
source. It can be a table, query, an SQL statement
if
you
choose Table/Query
as the row source type. You can also select a Value
list
which
is a typed in
list of values, or a Field list, which will show all
the
fields
in a table or
query.

The most common way to do this is with a query. If
you
want to
look up
values in a specific field in your table and the
values
are
unique, then you
can use something like this
SELECT FieldToSearch FROM TableToSearch;

Don't confuse a Control Source with a Row Source.
The
control
source is
used for binding the combo to a field. A Row Source
is
what
the combo
displays.

:

Yes but if i turn it into a combo and it is
unbound,
it
will
not do the "type
ahead thing" as it is not bound to anything.
Surely
it
would
have to be bound
in order for this to happen?

Also, I have added a date field to the form which
I
need
to
appear in the
reports. It show today's date but i cant get it to
write
to
the reports. Do
you understand where I am coming from?

How do I make it so that the date that the quote
was
issued
on has the date
with it?

Matt

:

Typically, you would use an unbound Combo. The
Combo
control is ideally
suited for doing this because it has the
Autoexpand
property which, when set
to Yes, does the "type ahead" thing and allows
the
user to
find the value
quickly without having to type in the entire
value.
It
also has the Not In
List property which either prevents or allows
users
to
enter values that are