Form and Query Questions

[Steve]

Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestock!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!FrmRestock!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!FrmRestock!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]

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:

DateAdd("d", - Forms!FrmRestock!LastXDays, Date())

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.

 

[Steve]

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

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:

DateAdd("d", - Forms!FrmRestock!LastXDays, Date())

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.

Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestock!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!FrmRestock!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!FrmRestock!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?

 

[Steve]

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.AllowAdditions = True
Me.Requery
Me.AllowAdditions = False

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

Steve

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:

DateAdd("d", - Forms!FrmRestock!LastXDays, Date())

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.

Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestock!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!FrmRestock!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!FrmRestock!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?

 

[Allen Browne]

Issue 1. No records returned.
Work on clarifying the data types for Access.

If you open the query directly, do you see the DaysSinceTrans left-aligned,
or right-aligned? If it is left-aligned, Access is interpreting it as text
(not number). To avoid this:
a) Change the field to:
DaysSinceTrans: CLng(Nz(DateDiff("d", [TransDate], Date()), 0)
b) Set the Format property of the combo to General Number.

These changes:
- solve any problem with time values in the dates;
- handle null values;
- ensure Access understands the calculated field is numeric;
- help Access understand the combo as numeric.

Issue 2. Faulty combo display
Access displays the detail section of a form completely blank if both:
a) there are no matching records to display, and
b) no new record can be added.

The Form Header and Form Footer sections do display, but there is a bug in
Access 2000 that causes it not to display the value in the combo in these
circumstances. If you go to the Immediate Window (Ctrl+G) and examine the
value of the combo, e.g.:
? Forms!MyForm!MyCombo
Access is quite confused about its state. If you requery the combo, you may
see the value display very briefly before the bug takes over and removes it
again. AFAIK, there is no solution for this in Access 2000.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

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:

DateAdd("d", - Forms!FrmRestock!LastXDays, Date())

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.

Form FrmRestock's recordsource is QryFrmRestock. The TransactionDate field's
criteria is set ats:
Forms!FrmRestock!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!FrmRestock!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!FrmRestock!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?