473,287 Members | 3,228 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Form and Query Questions

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
Nov 12 '05 #1
3 4949
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.
Tips for Access users - http://allenbrowne.com/tips.html

"Steve" <ss****@bellsouth.net> wrote in message
news:vF******************@newsread2.news.atl.earth link.net... 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?

Nov 12 '05 #2
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" <ab***************@bigpond.net.au> wrote in message
news:Ob*********************@news-server.bigpond.net.au...
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.
Tips for Access users - http://allenbrowne.com/tips.html

"Steve" <ss****@bellsouth.net> wrote in message
news:vF******************@newsread2.news.atl.earth link.net...
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?


Nov 12 '05 #3
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
"Allen Browne" <ab***************@bigpond.net.au> wrote in message
news:Ob*********************@news-server.bigpond.net.au...
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.
Tips for Access users - http://allenbrowne.com/tips.html

"Steve" <ss****@bellsouth.net> wrote in message
news:vF******************@newsread2.news.atl.earth link.net...
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?


Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
by: Pete..... | last post by:
Hi all. I have made a webpage where there is a webform where people can fill in their personel information: The code is below: I want to transfer the data to a postgreSQL database ( I have...
2
by: Sherman H. | last post by:
I have a few questions for crosstab and popup form questions: 1. I created a crosstab as follows: IT Financial Operation John 21 22 ...
14
by: alwayshouston | last post by:
Hi All! I am working on this very small database and I am confused in the designing a simple form. I only have three tables in the database. First Table: tblExpense Columns: ExpenseID ;...
1
by: meganrobertson22 | last post by:
hi everybody- what is the best way to add data from one form to another? i have 2 tables: person and contract. here are some of the fields. table: person personid (autonumber and primary...
1
by: jeffro | last post by:
I have a database used for recording survey data. In the database, I have a form that displays a survey participant and a subform that is filled in by selecting a question from a looklist and...
26
by: Jerim79 | last post by:
I need to create a form that takes a number that the user enters, and duplicates a question the number of times the user entered. For instance, if the customer enters 5 on the first page, when...
11
by: mrowe | last post by:
I am using Access 2003. (I am also using ADO in the vast majority of my code. I recently read a post that indicated that ADO is not all that is was initially cracked up to be. In the back of my...
8
by: tess | last post by:
I have: table 1 - tblLeadInfo which includes a salesman ID field table 2 - tbllkpSalesman with all zips in the state and a Salesman assigned to that area. I have a form based on table #1 When...
6
by: jonnyboy | last post by:
Hello, I have an Access application with a number of forms. On opening one particular form, the application will sometimes appear to get stuck in a loop with 'Calculating...' in the status bar for...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.