Form and Query Questions

[Steve]

Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestoc k!LastXDays. LastXDays on the form is a combobox where the
selections are 30, 60 and 90. The default is set at 30.

Question1: When the form opens, there are no records displayed although there
are many records that fit the criteria of 30. If I put a button on the form to
do a requery and press the button, all the records appear. Why don't the records
appear when the form opens?

Question2: If I put =Date()-TransactionDate as a calculated field in the query
and put <=Forms!FrmRest ock!LastXDays as the criteria, no matter what I select in
the combobox, I get the same records. However, if I put TransactionDate as a
field in the query and put >=Date()-Forms!FrmRestoc k!LastXDays as the criteria,
I get different records depending on whether I select 30, 60 or 90. Why does the
second way work and not the first?

Thanks!

Steve

[Allen Browne]

Re: Form and Query Questions

I'm confused about your data types, and I'm sure Access is as well.

Is TransactionDate a field in a table?
If so, open the table in design view, and see what the Data type is.
Is it "Date/Time"?
If so, and the combo contains 30, the only date that will match is Jan 29,
1900 (which has the value 30 in Access). Try setting the criteria in your
query to:[color=blue]
> DateAdd("d", - Forms!FrmRestoc k!LastXDays, Date())[/color]

If TransactionDate is a calculated field in your query, then wrap the
calculation in CVDate() so Access understands the data type, e.g.:
MyField: CVDate(Date() - [TransactionDate])

If TransactionDate is not a date at all, but a Number, wrap the calculation
in CLng() or something so Access knows the data type.

If the combo is unbound, you can also help Access understand that the combo
is supposed to be a number by setting its Format property to General Number.

If your query has parameters, be sure to declare them (Parameter on Query
menu), so you can specify the data type for them as well.

It's quite important to be explicit about your data types with calculated
fields, unbound controls, and parameters.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Steve" <ssmith@bellsou th.net> wrote in message
news:vFzfb.2785 0$3S.18831@news read2.news.atl. earthlink.net.. .[color=blue]
> Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate[/color]
field's[color=blue]
> criteria is set ats:
> Forms!FrmRestoc k!LastXDays. LastXDays on the form is a combobox where the
> selections are 30, 60 and 90. The default is set at 30.
>
> Question1: When the form opens, there are no records displayed although[/color]
there[color=blue]
> are many records that fit the criteria of 30. If I put a button on the[/color]
form to[color=blue]
> do a requery and press the button, all the records appear. Why don't the[/color]
records[color=blue]
> appear when the form opens?
>
> Question2: If I put =Date()-TransactionDate as a calculated field in the[/color]
query[color=blue]
> and put <=Forms!FrmRest ock!LastXDays as the criteria, no matter what I[/color]
select in[color=blue]
> the combobox, I get the same records. However, if I put TransactionDate as[/color]
a[color=blue]
> field in the query and put >=Date()-Forms!FrmRestoc k!LastXDays as the[/color]
criteria,[color=blue]
> I get different records depending on whether I select 30, 60 or 90. Why[/color]
does the[color=blue]
> second way work and not the first?[/color]

[Steve]

Re: Form and Query Questions

Allen,

Thank you for responding!

TransactionDate is a field in a table with DateTime data type. I have the
following calculated field in the query:
DaysSinceTrans: Date()-[TransDate]
The criteria for this field is:
<=[Forms]![PFrmRestock]![LastXDays]

LastXDays is a combobox in the form header with a ValuelIst rowsource and has
the values 30;60;90. The default is set for 60. The Afterupdate code for the
combobox is Me.Requery. With the data for TransDate, the query returns records
for 60 and 90 but no records for 30. So when the form is open, if I select 60 or
90 in the combobox, the form displays records and when I select 30 I get no
records as it should be.

Question1: Although the default for the combobox is set for 60 and there are
records for the selection of 60, when the form opens no records are displayed. I
have to go to the combobox and select 60 to get the records. Why don't the
resords display when the form opens? How do I get the records for 60 to display
when the form opens?

Question2: There are no records for the selection of 30. When the form is open
and I select 30, I get no records as expected but the 30 does not display in the
combobox after being selected. Why? I see this on another form too where I
select a criteria in a combobox in a form header. When no records are displayed,
the selection does not appear in the combobox. I also noticed that the cursor
does not appear blinking anywhere on the screen too.

Steve
"Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in message
news:ObAfb.1363 99$bo1.134622@n ews-server.bigpond. net.au...[color=blue]
> I'm confused about your data types, and I'm sure Access is as well.
>
> Is TransactionDate a field in a table?
> If so, open the table in design view, and see what the Data type is.
> Is it "Date/Time"?
> If so, and the combo contains 30, the only date that will match is Jan 29,
> 1900 (which has the value 30 in Access). Try setting the criteria in your
> query to:[color=green]
> > DateAdd("d", - Forms!FrmRestoc k!LastXDays, Date())[/color]
>
> If TransactionDate is a calculated field in your query, then wrap the
> calculation in CVDate() so Access understands the data type, e.g.:
> MyField: CVDate(Date() - [TransactionDate])
>
> If TransactionDate is not a date at all, but a Number, wrap the calculation
> in CLng() or something so Access knows the data type.
>
> If the combo is unbound, you can also help Access understand that the combo
> is supposed to be a number by setting its Format property to General Number.
>
> If your query has parameters, be sure to declare them (Parameter on Query
> menu), so you can specify the data type for them as well.
>
> It's quite important to be explicit about your data types with calculated
> fields, unbound controls, and parameters.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
>
> "Steve" <ssmith@bellsou th.net> wrote in message
> news:vFzfb.2785 0$3S.18831@news read2.news.atl. earthlink.net.. .[color=green]
> > Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate[/color]
> field's[color=green]
> > criteria is set ats:
> > Forms!FrmRestoc k!LastXDays. LastXDays on the form is a combobox where the
> > selections are 30, 60 and 90. The default is set at 30.
> >
> > Question1: When the form opens, there are no records displayed although[/color]
> there[color=green]
> > are many records that fit the criteria of 30. If I put a button on the[/color]
> form to[color=green]
> > do a requery and press the button, all the records appear. Why don't the[/color]
> records[color=green]
> > appear when the form opens?
> >
> > Question2: If I put =Date()-TransactionDate as a calculated field in the[/color]
> query[color=green]
> > and put <=Forms!FrmRest ock!LastXDays as the criteria, no matter what I[/color]
> select in[color=green]
> > the combobox, I get the same records. However, if I put TransactionDate as[/color]
> a[color=green]
> > field in the query and put >=Date()-Forms!FrmRestoc k!LastXDays as the[/color]
> criteria,[color=green]
> > I get different records depending on whether I select 30, 60 or 90. Why[/color]
> does the[color=green]
> > second way work and not the first?[/color]
>
>[/color]

[Steve]

Re: Form and Query Questions

Allen:

Found the answer to both problems!!

1. The value of the combobox is not available until the form opens. Therefore,
the reference to the combobox in the criteria of the query which is the
recordsource of the form has a null value which causes the query to not return
any records when the form opens.

2. The purpose of the form is to display the inventory and reorder point of
products in the database. Therefore, I had turned of Allow Additions. When Allow
Additions is turned off and a selection is made in the combobox which returns no
records, Allow Additions being turned off causes the selection not to be
displayed in the combobox. The work around is to change the code in the
AfterUpdate of the combobox to:
Me.AllowAdditio ns = True
Me.Requery
Me.AllowAdditio ns = False

By changing this code, all selections whether they return records or not are
displayed in the combobox.

Steve


"Allen Browne" <abrowne1_SpamT rap@bigpond.net .au> wrote in message
news:ObAfb.1363 99$bo1.134622@n ews-server.bigpond. net.au...[color=blue]
> I'm confused about your data types, and I'm sure Access is as well.
>
> Is TransactionDate a field in a table?
> If so, open the table in design view, and see what the Data type is.
> Is it "Date/Time"?
> If so, and the combo contains 30, the only date that will match is Jan 29,
> 1900 (which has the value 30 in Access). Try setting the criteria in your
> query to:[color=green]
> > DateAdd("d", - Forms!FrmRestoc k!LastXDays, Date())[/color]
>
> If TransactionDate is a calculated field in your query, then wrap the
> calculation in CVDate() so Access understands the data type, e.g.:
> MyField: CVDate(Date() - [TransactionDate])
>
> If TransactionDate is not a date at all, but a Number, wrap the calculation
> in CLng() or something so Access knows the data type.
>
> If the combo is unbound, you can also help Access understand that the combo
> is supposed to be a number by setting its Format property to General Number.
>
> If your query has parameters, be sure to declare them (Parameter on Query
> menu), so you can specify the data type for them as well.
>
> It's quite important to be explicit about your data types with calculated
> fields, unbound controls, and parameters.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
>
> "Steve" <ssmith@bellsou th.net> wrote in message
> news:vFzfb.2785 0$3S.18831@news read2.news.atl. earthlink.net.. .[color=green]
> > Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate[/color]
> field's[color=green]
> > criteria is set ats:
> > Forms!FrmRestoc k!LastXDays. LastXDays on the form is a combobox where the
> > selections are 30, 60 and 90. The default is set at 30.
> >
> > Question1: When the form opens, there are no records displayed although[/color]
> there[color=green]
> > are many records that fit the criteria of 30. If I put a button on the[/color]
> form to[color=green]
> > do a requery and press the button, all the records appear. Why don't the[/color]
> records[color=green]
> > appear when the form opens?
> >
> > Question2: If I put =Date()-TransactionDate as a calculated field in the[/color]
> query[color=green]
> > and put <=Forms!FrmRest ock!LastXDays as the criteria, no matter what I[/color]
> select in[color=green]
> > the combobox, I get the same records. However, if I put TransactionDate as[/color]
> a[color=green]
> > field in the query and put >=Date()-Forms!FrmRestoc k!LastXDays as the[/color]
> criteria,[color=green]
> > I get different records depending on whether I select 30, 60 or 90. Why[/color]
> does the[color=green]
> > second way work and not the first?[/color]
>
>[/color]