Form StartDate / EndDate Concern – Two Additional “Enter Parameter

J

Jay

I am new to developing Access 2003 databases (first attemptïŠ) and have
encountered a problem using the StartDate and EndDate boxes on my form.
Please excuse the length of my post. I have included all data I felt might
be pertinent in the hope that a discussion board member may spot my error(s).

Problem: When I enter the StartDate and EndDate (2 separate text boxes) on
the “CONTRACT BY ICRF SUBMIT DATE FORM†form, and click the “OK†command
button, the following happens:
1. “Enter Parameter Value†box pops up with a StartDate entry area. I enter
start date and click “OK†command button
2. “Enter Parameter Value†box pops up with an EndDate entry area. I enter
end date and click “OK†command button
3. “CONTRACT BY ICRF SUBMIT DATE Query: Select Query†is returned.
The data in the returned “CONTRACT BY ICRF SUBMIT DATE Query: Select Queryâ€
is correct, but I can not figure out why after I enter dates into my
StartDate and EndDate text boxes on my form, the “Enter Parameter Valueâ€
boxes appear and I have to enter the dates again. I have enclosed the basics
of my form layout in the hope that you can help me determine where my
error(s) reside.

My database contains the following:
• Tables
o Main table titled A_IAP_MAIN
o Thirteen separate tables that support A_IAP_MAIN table and DATA_ENTRY form
combo boxes
• Forms
o DATA_ENTRY form used to populate A_IAP_MAIN
o CONTRACT BY ICRF SUBMIT DATE FORM form (form I am having concern with)

MAIN TABLE (A_IAP_MAIN) FIELDS USED ON “CONTRACT BY ICRF SUBMIT DATE FORMâ€
FORM
• O_01_CONTRACT_IMPACTED field properties
o Location: Resides as field within database main table (A_IAP_MAIN)
 Feeder: Separate table titled “O_01_CONTRACT_IMPACTED†that populates
combo box field
o O_01_CONTRACT_IMPACTED field properties:
 Database main table (A_IAP_MAIN) field title: O_01_CONTRACT_IMPACTED
 Data Type: Number
 General Tab
• Field Size: Long Integer
• Decimal Places: 0
• Caption: O-01_CONTRACT_IMPACTED
• Default Values: 0
• Required: No
• Indexed: Yes (Duplicates OK)
 Lookup Tab
• Display Control: Combo Box
• Row Source Type: Table/Query
• Row Source: SELECT O_01_CONTRACT_IMPACTED.ID,
O_01_CONTRACT_IMPACTED.CONTRACT_IMPACTED FROM O_01_CONTRACT_IMPACTED ORDER BY
[CONTRACT_IMPACTED];
• Bound Column: 1
• Column Count: 2
• Column Heads: No
• List Rows: 8
• Limit To List: Yes
• O_17_FORM_SUBMITTAL_DATE
o Location: Resides as field within database main table (A_IAP_MAIN)
o Database main table (A_IAP_MAIN) field title: O_17_FORM_SUBMITTAL_DATE
o O_17_FORM_SUBMITTAL_DATE field properties
 Data Type: Date/Time
 General Tab
• Format: Short Date
• Input Mask: 99/99/0000;0;_
• Caption: O_17_FORM_SUBMITTAL_DATE
• Required: No
• Indexed: Yes (Duplicates OK)
• IME Mode: No Control
• IME Sentence Mode: No
 Lookup Tab: Not applicable

“CONTRACT BY ICRF SUBMIT DATE FORM†FORM
• CONTRACT BY ICRF FORM properties (one Combo Box [CONTRACT_IMPACTED], two
Text Boxes [StartDate and EndDate], and two Command Buttons [OK and CANCEL])
o Combo Box (titled CONTRACT_IMPACTED)
 All Tab
• Name:
• Control Source: O_01_CONTRACT_IMPACTED
• Decimal Places: Auto
• Row Source Type: Table/Query
• Row Source: SELECT [O_01_CONTRACT_IMPACTED].[ID],
[O_01_CONTRACT_IMPACTED].[CONTRACT_IMPACTED] FROM [O_01_CONTRACT_IMPACTED]
ORDER BY [CONTRACT_IMPACTED];
o Text Box: StartDate
 All Tab
• Name: StartDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Text Box: EndDate
 All Tab
• Name: EndDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: OK
 Name: OK
 Caption: OK
 Transparent: No
 Default: No
 Cancel: No
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 3
 Tag: OK
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: CANCEL
 Name: CANCEL
 Caption: CANCEL
 Default: No
 Cancel: Yes
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 4
 Tag: CANCEL
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub

“CONTRACT BY ICRF SUBMIT DATE QUERY†Query
• Field 1 properties
o Field: ID
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria:
o Or:
• Field 2 properties
o Field: O_01_CONTRACT_IMPACTED
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria: [Forms]![CONTRACT BY ICRF SUBMIT DATE
FORM]![O_01_CONTRACT_IMPACTED]
o Or:
• Field 3 properties
o Field:
o Table: A_IAP_MAIN
o Sort: Descending
o Show: Checked
o Criteria: Between [StartDate] And [EndDate]
o Or:
 
J

Jeff L

Here's your problem
Field 3 properties
Criteria: Between [StartDate] And [EndDate]

The criteria should be
Between Forms![CONTRACT BY ICRF SUBMIT DATE FORM]![StartDate] And
Forms![CONTRACT BY ICRF SUBMIT DATE FORM]![EndDate]

Hope that helps!


I am new to developing Access 2003 databases (first attemptïŠ) andhave
encountered a problem using the StartDate and EndDate boxes on my form.
Please excuse the length of my post. I have included all data I felt might
be pertinent in the hope that a discussion board member may spot my error(s).

Problem: When I enter the StartDate and EndDate (2 separate text boxes) on
the “CONTRACT BY ICRF SUBMIT DATE FORM†form, and click the “OK†command
button, the following happens:
1. “Enter Parameter Value†box pops up with a StartDate entry area. I enter
start date and click “OK†command button
2. “Enter Parameter Value†box pops up with an EndDate entry area. I enter
end date and click “OK†command button
3. “CONTRACT BY ICRF SUBMIT DATE Query: Select Query†is returned.
The data in the returned “CONTRACT BY ICRF SUBMIT DATE Query: Select Queryâ€
is correct, but I can not figure out why after I enter dates into my
StartDate and EndDate text boxes on my form, the “Enter ParameterValueâ€
boxes appear and I have to enter the dates again. I have enclosed the basics
of my form layout in the hope that you can help me determine where my
error(s) reside.

My database contains the following:
• Tables
o Main table titled A_IAP_MAIN
o Thirteen separate tables that support A_IAP_MAIN table and DATA_ENTRY form
combo boxes
• Forms
o DATA_ENTRY form used to populate A_IAP_MAIN
o CONTRACT BY ICRF SUBMIT DATE FORM form (form I am having concern with)

MAIN TABLE (A_IAP_MAIN) FIELDS USED ON “CONTRACT BY ICRF SUBMIT DATE FORMâ€
FORM
• O_01_CONTRACT_IMPACTED field properties
o Location: Resides as field within database main table (A_IAP_MAIN)
 Feeder: Separate table titled “O_01_CONTRACT_IMPACTED†that populates
combo box field
o O_01_CONTRACT_IMPACTED field properties:
 Database main table (A_IAP_MAIN) field title: O_01_CONTRACT_IMPACTED
 Data Type: Number
 General Tab
• Field Size: Long Integer
• Decimal Places: 0
• Caption: O-01_CONTRACT_IMPACTED
• Default Values: 0
• Required: No
• Indexed: Yes (Duplicates OK)
 Lookup Tab
• Display Control: Combo Box
• Row Source Type: Table/Query
• Row Source: SELECT O_01_CONTRACT_IMPACTED.ID,
O_01_CONTRACT_IMPACTED.CONTRACT_IMPACTED FROM O_01_CONTRACT_IMPACTED ORDER BY
[CONTRACT_IMPACTED];
• Bound Column: 1
• Column Count: 2
• Column Heads: No
• List Rows: 8
• Limit To List: Yes
• O_17_FORM_SUBMITTAL_DATE
o Location: Resides as field within database main table (A_IAP_MAIN)
o Database main table (A_IAP_MAIN) field title: O_17_FORM_SUBMITTAL_DATE
o O_17_FORM_SUBMITTAL_DATE field properties
 Data Type: Date/Time
 General Tab
• Format: Short Date
• Input Mask: 99/99/0000;0;_
• Caption: O_17_FORM_SUBMITTAL_DATE
• Required: No
• Indexed: Yes (Duplicates OK)
• IME Mode: No Control
• IME Sentence Mode: No
 Lookup Tab: Not applicable

“CONTRACT BY ICRF SUBMIT DATE FORM†FORM
• CONTRACT BY ICRF FORM properties (one Combo Box [CONTRACT_IMPACTED], two
Text Boxes [StartDate and EndDate], and two Command Buttons [OK and CANCEL])
o Combo Box (titled CONTRACT_IMPACTED)
 All Tab
• Name:
• Control Source: O_01_CONTRACT_IMPACTED
• Decimal Places: Auto
• Row Source Type: Table/Query
• Row Source: SELECT [O_01_CONTRACT_IMPACTED].[ID],
[O_01_CONTRACT_IMPACTED].[CONTRACT_IMPACTED] FROM [O_01_CONTRACT_IMPACTED]
ORDER BY [CONTRACT_IMPACTED];
o Text Box: StartDate
 All Tab
• Name: StartDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Text Box: EndDate
 All Tab
• Name: EndDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: OK
 Name: OK
 Caption: OK
 Transparent: No
 Default: No
 Cancel: No
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 3
 Tag: OK
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: CANCEL
 Name: CANCEL
 Caption: CANCEL
 Default: No
 Cancel: Yes
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 4
 Tag: CANCEL
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub

“CONTRACT BY ICRF SUBMIT DATE QUERY†Query
• Field 1 properties
o Field: ID
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria:
o Or:
• Field 2 properties
o Field: O_01_CONTRACT_IMPACTED
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria: [Forms]![CONTRACT BY ICRF SUBMIT DATE
FORM]![O_01_CONTRACT_IMPACTED]
o Or:
• Field 3 properties
o Field:
o Table: A_IAP_MAIN
o Sort: Descending
o Show: Checked
o Criteria: Between [StartDate] And [EndDate]
o Or:
Click to expand...
 
J

Jay

Hi Jeff L,

Thank you for the rapid response.

I added the text you provided to Field 3 (O_17_FORM-SUBMITTAL-DATE) on my
“CONTRACT BY ICRF SUBMIT DATE QUERY†query. Now when I use my “CONTRACT BY
ICRF SUBMIT DATE FORMâ€, my StartDate and EndDate text boxes are working.

Somehow, the change affected the combo box (CONTRACT_IMPACTED) usability.
Now when I select a contract option from the form combo box and enter dates
in my StartDate and EndDate text boxes, the returned “CONTRACT BY ICRF SUBMIT
DATE QUERY†is returned blank.

Could I have an error in Field 2 (O_01_CONTRACT_IMPACTED) criteria block on
the “CONTRACT BY ICRF SUBMIT DATE QUERY�

--
Thank you,
Jay


Jay said:
I am new to developing Access 2003 databases (first attemptïŠ) and have
encountered a problem using the StartDate and EndDate boxes on my form.
Please excuse the length of my post. I have included all data I felt might
be pertinent in the hope that a discussion board member may spot my error(s).

Problem: When I enter the StartDate and EndDate (2 separate text boxes) on
the “CONTRACT BY ICRF SUBMIT DATE FORM†form, and click the “OK†command
button, the following happens:
1. “Enter Parameter Value†box pops up with a StartDate entry area. I enter
start date and click “OK†command button
2. “Enter Parameter Value†box pops up with an EndDate entry area. I enter
end date and click “OK†command button
3. “CONTRACT BY ICRF SUBMIT DATE Query: Select Query†is returned.
The data in the returned “CONTRACT BY ICRF SUBMIT DATE Query: Select Queryâ€
is correct, but I can not figure out why after I enter dates into my
StartDate and EndDate text boxes on my form, the “Enter Parameter Valueâ€
boxes appear and I have to enter the dates again. I have enclosed the basics
of my form layout in the hope that you can help me determine where my
error(s) reside.

My database contains the following:
• Tables
o Main table titled A_IAP_MAIN
o Thirteen separate tables that support A_IAP_MAIN table and DATA_ENTRY form
combo boxes
• Forms
o DATA_ENTRY form used to populate A_IAP_MAIN
o CONTRACT BY ICRF SUBMIT DATE FORM form (form I am having concern with)

MAIN TABLE (A_IAP_MAIN) FIELDS USED ON “CONTRACT BY ICRF SUBMIT DATE FORMâ€
FORM
• O_01_CONTRACT_IMPACTED field properties
o Location: Resides as field within database main table (A_IAP_MAIN)
 Feeder: Separate table titled “O_01_CONTRACT_IMPACTED†that populates
combo box field
o O_01_CONTRACT_IMPACTED field properties:
 Database main table (A_IAP_MAIN) field title: O_01_CONTRACT_IMPACTED
 Data Type: Number
 General Tab
• Field Size: Long Integer
• Decimal Places: 0
• Caption: O-01_CONTRACT_IMPACTED
• Default Values: 0
• Required: No
• Indexed: Yes (Duplicates OK)
 Lookup Tab
• Display Control: Combo Box
• Row Source Type: Table/Query
• Row Source: SELECT O_01_CONTRACT_IMPACTED.ID,
O_01_CONTRACT_IMPACTED.CONTRACT_IMPACTED FROM O_01_CONTRACT_IMPACTED ORDER BY
[CONTRACT_IMPACTED];
• Bound Column: 1
• Column Count: 2
• Column Heads: No
• List Rows: 8
• Limit To List: Yes
• O_17_FORM_SUBMITTAL_DATE
o Location: Resides as field within database main table (A_IAP_MAIN)
o Database main table (A_IAP_MAIN) field title: O_17_FORM_SUBMITTAL_DATE
o O_17_FORM_SUBMITTAL_DATE field properties
 Data Type: Date/Time
 General Tab
• Format: Short Date
• Input Mask: 99/99/0000;0;_
• Caption: O_17_FORM_SUBMITTAL_DATE
• Required: No
• Indexed: Yes (Duplicates OK)
• IME Mode: No Control
• IME Sentence Mode: No
 Lookup Tab: Not applicable

“CONTRACT BY ICRF SUBMIT DATE FORM†FORM
• CONTRACT BY ICRF FORM properties (one Combo Box [CONTRACT_IMPACTED], two
Text Boxes [StartDate and EndDate], and two Command Buttons [OK and CANCEL])
o Combo Box (titled CONTRACT_IMPACTED)
 All Tab
• Name:
• Control Source: O_01_CONTRACT_IMPACTED
• Decimal Places: Auto
• Row Source Type: Table/Query
• Row Source: SELECT [O_01_CONTRACT_IMPACTED].[ID],
[O_01_CONTRACT_IMPACTED].[CONTRACT_IMPACTED] FROM [O_01_CONTRACT_IMPACTED]
ORDER BY [CONTRACT_IMPACTED];
o Text Box: StartDate
 All Tab
• Name: StartDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Text Box: EndDate
 All Tab
• Name: EndDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: OK
 Name: OK
 Caption: OK
 Transparent: No
 Default: No
 Cancel: No
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 3
 Tag: OK
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: CANCEL
 Name: CANCEL
 Caption: CANCEL
 Default: No
 Cancel: Yes
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 4
 Tag: CANCEL
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub

“CONTRACT BY ICRF SUBMIT DATE QUERY†Query
• Field 1 properties
o Field: ID
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria:
o Or:
• Field 2 properties
o Field: O_01_CONTRACT_IMPACTED
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria: [Forms]![CONTRACT BY ICRF SUBMIT DATE
FORM]![O_01_CONTRACT_IMPACTED]
o Or:
• Field 3 properties
o Field:
o Table: A_IAP_MAIN
o Sort: Descending
o Show: Checked
o Criteria: Between [StartDate] And [EndDate]
o Or:
Click to expand...
 
J

Jeff L

Usually, when a query is run and the results are blank it means that
there are no records in the query that meet the criteria you have set.
Check your criteria and make sure it's correct.


Hi Jeff L,

Thank you for the rapid response.

I added the text you provided to Field 3 (O_17_FORM-SUBMITTAL-DATE) on my
“CONTRACT BY ICRF SUBMIT DATE QUERY†query. Now when I use my “CONTRACT BY
ICRF SUBMIT DATE FORMâ€, my StartDate and EndDate text boxes are working.

Somehow, the change affected the combo box (CONTRACT_IMPACTED) usability.
Now when I select a contract option from the form combo box and enter dates
in my StartDate and EndDate text boxes, the returned “CONTRACT BYICRF SUBMIT
DATE QUERY†is returned blank.

Could I have an error in Field 2 (O_01_CONTRACT_IMPACTED) criteria block on
the “CONTRACT BY ICRF SUBMIT DATE QUERY�

--
Thank you,
Jay


Jay said:
I am new to developing Access 2003 databases (first attemptïŠ) and have
encountered a problem using the StartDate and EndDate boxes on my form.
Please excuse the length of my post. I have included all data I felt might
be pertinent in the hope that a discussion board member may spot my error(s).

Problem: When I enter the StartDate and EndDate (2 separate text boxes) on
the “CONTRACT BY ICRF SUBMIT DATE FORM†form, and clickthe “OK†command
button, the following happens:
1. “Enter Parameter Value†box pops up with a StartDateentry area. I enter
start date and click “OK†command button
2. “Enter Parameter Value†box pops up with an EndDate entry area. I enter
end date and click “OK†command button
3. “CONTRACT BY ICRF SUBMIT DATE Query: Select Query†is returned.
The data in the returned “CONTRACT BY ICRF SUBMIT DATE Query: Select Queryâ€
is correct, but I can not figure out why after I enter dates into my
StartDate and EndDate text boxes on my form, the “Enter Parameter Valueâ€
boxes appear and I have to enter the dates again. I have enclosed the basics
of my form layout in the hope that you can help me determine where my
error(s) reside.

My database contains the following:
• Tables
o Main table titled A_IAP_MAIN
o Thirteen separate tables that support A_IAP_MAIN table and DATA_ENTRYform
combo boxes
• Forms
o DATA_ENTRY form used to populate A_IAP_MAIN
o CONTRACT BY ICRF SUBMIT DATE FORM form (form I am having concern with)

MAIN TABLE (A_IAP_MAIN) FIELDS USED ON “CONTRACT BY ICRF SUBMITDATE FORMâ€
FORM
• O_01_CONTRACT_IMPACTED field properties
o Location: Resides as field within database main table (A_IAP_MAIN)
 Feeder: Separate table titled “O_01_CONTRACT_IMPACTED†that populates
combo box field
o O_01_CONTRACT_IMPACTED field properties:
 Database main table (A_IAP_MAIN) field title: O_01_CONTRACT_IMPACTED
 Data Type: Number
 General Tab
• Field Size: Long Integer
• Decimal Places: 0
• Caption: O-01_CONTRACT_IMPACTED
• Default Values: 0
• Required: No
• Indexed: Yes (Duplicates OK)
 Lookup Tab
• Display Control: Combo Box
• Row Source Type: Table/Query
• Row Source: SELECT O_01_CONTRACT_IMPACTED.ID,
O_01_CONTRACT_IMPACTED.CONTRACT_IMPACTED FROM O_01_CONTRACT_IMPACTED ORDER BY
[CONTRACT_IMPACTED];
• Bound Column: 1
• Column Count: 2
• Column Heads: No
• List Rows: 8
• Limit To List: Yes
• O_17_FORM_SUBMITTAL_DATE
o Location: Resides as field within database main table (A_IAP_MAIN)
o Database main table (A_IAP_MAIN) field title: O_17_FORM_SUBMITTAL_DATE
o O_17_FORM_SUBMITTAL_DATE field properties
 Data Type: Date/Time
 General Tab
• Format: Short Date
• Input Mask: 99/99/0000;0;_
• Caption: O_17_FORM_SUBMITTAL_DATE
• Required: No
• Indexed: Yes (Duplicates OK)
• IME Mode: No Control
• IME Sentence Mode: No
 Lookup Tab: Not applicable

“CONTRACT BY ICRF SUBMIT DATE FORM†FORM
• CONTRACT BY ICRF FORM properties (one Combo Box [CONTRACT_IMPACTED], two
Text Boxes [StartDate and EndDate], and two Command Buttons [OK and CANCEL])
o Combo Box (titled CONTRACT_IMPACTED)
 All Tab
• Name:
• Control Source: O_01_CONTRACT_IMPACTED
• Decimal Places: Auto
• Row Source Type: Table/Query
• Row Source: SELECT [O_01_CONTRACT_IMPACTED].[ID],
[O_01_CONTRACT_IMPACTED].[CONTRACT_IMPACTED] FROM [O_01_CONTRACT_IMPACTED]
ORDER BY [CONTRACT_IMPACTED];
o Text Box: StartDate
 All Tab
• Name: StartDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Text Box: EndDate
 All Tab
• Name: EndDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: OK
 Name: OK
 Caption: OK
 Transparent: No
 Default: No
 Cancel: No
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 3
 Tag: OK
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: CANCEL
 Name: CANCEL
 Caption: CANCEL
 Default: No
 Cancel: Yes
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 4
 Tag: CANCEL
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub

“CONTRACT BY ICRF SUBMIT DATE QUERY†Query
• Field 1 properties
o Field: ID
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria:
o Or:
• Field 2 properties
o Field: O_01_CONTRACT_IMPACTED
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria: [Forms]![CONTRACT BY ICRF SUBMIT DATE
FORM]![O_01_CONTRACT_IMPACTED]
o Or:
• Field 3 properties
o Field:
o Table: A_IAP_MAIN
o Sort: Descending
o Show: Checked
o Criteria: Between [StartDate] And [EndDate]
o Or:
Click to expand...
Click to expand...
 
J

Jay

Hi Jeff L

Thank you for helping me with the form StartDate/EndDate concern I was
experiencing. I pasted the code you sent me into the criteria and found
another error I made & now it works great!

--
Thank you,
Jay


Jeff L said:
Usually, when a query is run and the results are blank it means that
there are no records in the query that meet the criteria you have set.
Check your criteria and make sure it's correct.


Hi Jeff L,

Thank you for the rapid response.

I added the text you provided to Field 3 (O_17_FORM-SUBMITTAL-DATE) on my
“CONTRACT BY ICRF SUBMIT DATE QUERY†query. Now when I use my “CONTRACT BY
ICRF SUBMIT DATE FORMâ€, my StartDate and EndDate text boxes are working.

Somehow, the change affected the combo box (CONTRACT_IMPACTED) usability.
Now when I select a contract option from the form combo box and enter dates
in my StartDate and EndDate text boxes, the returned “CONTRACT BY ICRF SUBMIT
DATE QUERY†is returned blank.

Could I have an error in Field 2 (O_01_CONTRACT_IMPACTED) criteria block on
the “CONTRACT BY ICRF SUBMIT DATE QUERY�

--
Thank you,
Jay


Jay said:
I am new to developing Access 2003 databases (first attemptïŠ) and have
encountered a problem using the StartDate and EndDate boxes on my form.
Please excuse the length of my post. I have included all data I felt might
be pertinent in the hope that a discussion board member may spot my error(s).

Problem: When I enter the StartDate and EndDate (2 separate text boxes) on
the “CONTRACT BY ICRF SUBMIT DATE FORM†form, and click the “OK†command
button, the following happens:
1. “Enter Parameter Value†box pops up with a StartDate entry area. I enter
start date and click “OK†command button
2. “Enter Parameter Value†box pops up with an EndDate entry area. I enter
end date and click “OK†command button
3. “CONTRACT BY ICRF SUBMIT DATE Query: Select Query†is returned.
The data in the returned “CONTRACT BY ICRF SUBMIT DATE Query: Select Queryâ€
is correct, but I can not figure out why after I enter dates into my
StartDate and EndDate text boxes on my form, the “Enter Parameter Valueâ€
boxes appear and I have to enter the dates again. I have enclosed the basics
of my form layout in the hope that you can help me determine where my
error(s) reside.

My database contains the following:
• Tables
o Main table titled A_IAP_MAIN
o Thirteen separate tables that support A_IAP_MAIN table and DATA_ENTRY form
combo boxes
• Forms
o DATA_ENTRY form used to populate A_IAP_MAIN
o CONTRACT BY ICRF SUBMIT DATE FORM form (form I am having concern with)

MAIN TABLE (A_IAP_MAIN) FIELDS USED ON “CONTRACT BY ICRF SUBMIT DATE FORMâ€
FORM
• O_01_CONTRACT_IMPACTED field properties
o Location: Resides as field within database main table (A_IAP_MAIN)
 Feeder: Separate table titled “O_01_CONTRACT_IMPACTED†that populates
combo box field
o O_01_CONTRACT_IMPACTED field properties:
 Database main table (A_IAP_MAIN) field title: O_01_CONTRACT_IMPACTED
 Data Type: Number
 General Tab
• Field Size: Long Integer
• Decimal Places: 0
• Caption: O-01_CONTRACT_IMPACTED
• Default Values: 0
• Required: No
• Indexed: Yes (Duplicates OK)
 Lookup Tab
• Display Control: Combo Box
• Row Source Type: Table/Query
• Row Source: SELECT O_01_CONTRACT_IMPACTED.ID,
O_01_CONTRACT_IMPACTED.CONTRACT_IMPACTED FROM O_01_CONTRACT_IMPACTED ORDER BY
[CONTRACT_IMPACTED];
• Bound Column: 1
• Column Count: 2
• Column Heads: No
• List Rows: 8
• Limit To List: Yes
• O_17_FORM_SUBMITTAL_DATE
o Location: Resides as field within database main table (A_IAP_MAIN)
o Database main table (A_IAP_MAIN) field title: O_17_FORM_SUBMITTAL_DATE
o O_17_FORM_SUBMITTAL_DATE field properties
 Data Type: Date/Time
 General Tab
• Format: Short Date
• Input Mask: 99/99/0000;0;_
• Caption: O_17_FORM_SUBMITTAL_DATE
• Required: No
• Indexed: Yes (Duplicates OK)
• IME Mode: No Control
• IME Sentence Mode: No
 Lookup Tab: Not applicable

“CONTRACT BY ICRF SUBMIT DATE FORM†FORM
• CONTRACT BY ICRF FORM properties (one Combo Box [CONTRACT_IMPACTED], two
Text Boxes [StartDate and EndDate], and two Command Buttons [OK and CANCEL])
o Combo Box (titled CONTRACT_IMPACTED)
 All Tab
• Name:
• Control Source: O_01_CONTRACT_IMPACTED
• Decimal Places: Auto
• Row Source Type: Table/Query
• Row Source: SELECT [O_01_CONTRACT_IMPACTED].[ID],
[O_01_CONTRACT_IMPACTED].[CONTRACT_IMPACTED] FROM [O_01_CONTRACT_IMPACTED]
ORDER BY [CONTRACT_IMPACTED];
o Text Box: StartDate
 All Tab
• Name: StartDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Text Box: EndDate
 All Tab
• Name: EndDate
• Control Source: O_17_FORM_SUBMITTAL_DATE
• Format: Short date
• Decimal Places: Auto
• Input Mask: 99/99/0000;0;_
• IME Sentence Mode: Phrase Predict
• Before Update: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close ‘Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery “CONTRACT BY ICRF SUBMIT DATE Queryâ€, acViewNormal, acEdit
DoCmd.Close acForm, “CONTRACT BY ICRF SUBMIT DATE Formâ€
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: OK
 Name: OK
 Caption: OK
 Transparent: No
 Default: No
 Cancel: No
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 3
 Tag: OK
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub
o Command Button: CANCEL
 Name: CANCEL
 Caption: CANCEL
 Default: No
 Cancel: Yes
 Auto Repeat: No
 Enabled: Yes
 Tab Stop: Yes
 Tab Index: 4
 Tag: CANCEL
 On Click: [Event Procedure]
Option Compare Database
Private Sub Cancel_Click()
DoCmd.Close 'Close Form
End Sub

Private Sub EndDate_BeforeUpdate(CANCEL As Integer)

End Sub

Private Sub OK_Click()
Me.Visible = False
DoCmd.OpenQuery "CONTRACT BY ICRF SUBMIT DATE Query", acViewNormal, acEdit
DoCmd.Close acForm, "CONTRACT BY ICRF SUBMIT DATE Form"
End Sub

Private Sub StartDate_BeforeUpdate(CANCEL As Integer)

End Sub

“CONTRACT BY ICRF SUBMIT DATE QUERY†Query
• Field 1 properties
o Field: ID
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria:
o Or:
• Field 2 properties
o Field: O_01_CONTRACT_IMPACTED
o Table: A_IAP_MAIN
o Sort:
o Show: Checked
o Criteria: [Forms]![CONTRACT BY ICRF SUBMIT DATE
FORM]![O_01_CONTRACT_IMPACTED]
o Or:
• Field 3 properties
o Field:
o Table: A_IAP_MAIN
o Sort: Descending
o Show: Checked
o Criteria: Between [StartDate] And [EndDate]
o Or:
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to enter Start- and Enddate in a form with subforms once only 3
Start- and end date in a form with subforms 1
Help with custom search form - should I use rec 2
Excactly jump to the field being found. 7
Same date range in main and sub form 10
Testing for Password on a Form 1
Form Sql May Be Causing Corruption 0
Obtaining Forward EndDate with StartDate and Number of Days e.g. 30 (Number of days(30) excludes wee 1

Top