Parameter Query

[Brook Morris via AccessMonster.com]

I have a problem I have been trying to work out with several queries that
have the following date parameter so people can open the query within the
appropriate date range.

=[forms]![REPORTING PARAMETERS]![txtDateFrom] And <=[forms]![REPORTING PARAMETERS]![txtDateTo]

When I execute the query icon the parameter query box comes up and I enter
the from date, enter, then the to date and enter. It goes right back to the
enter parameter start date.

Any help would greatly appreciated.

Brook

 

[Guest]

from date -- to date -- start date
Three prompts?

Post you SQL statement.

I have a problem I have been trying to work out with several queries that
have the following date parameter so people can open the query within the
appropriate date range.

=[forms]![REPORTING PARAMETERS]![txtDateFrom] And <=[forms]![REPORTING PARAMETERS]![txtDateTo]

When I execute the query icon the parameter query box comes up and I enter
the from date, enter, then the to date and enter. It goes right back to the
enter parameter start date.

Any help would greatly appreciated.

Brook

 

[Brook Morris via AccessMonster.com]

Karl,

Here is the code for the item that keeps giving me problems. I will enter
the "date From" information "enter", then enter the "date to" information
then it cycles right back to the begininning prompt for the "date from".

Private Sub HEARING_LOSS_Click()
On Error GoTo Err_HEARING_LOSS_Click

Dim stDocName As String

stDocName = "HEARING LOSS"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_HEARING_LOSS_Click:
Exit Sub

Err_HEARING_LOSS_Click:
MsgBox Err.Description
Resume Exit_HEARING_LOSS_Click

End Sub

Thanks for your help
Brook

Three prompts?

Post you SQL statement.

I have a problem I have been trying to work out with several queries that
have the following date parameter so people can open the query within the

[quoted text clipped - 9 lines]

Brook

 

[Guest]

Post the SQL form your query stDocName.
Open the query in design view, click on menu VIEW - SQL View, hightlight
all, copy, and paste in a post.

Karl,

Here is the code for the item that keeps giving me problems. I will enter
the "date From" information "enter", then enter the "date to" information
then it cycles right back to the begininning prompt for the "date from".

Private Sub HEARING_LOSS_Click()
On Error GoTo Err_HEARING_LOSS_Click

Dim stDocName As String

stDocName = "HEARING LOSS"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_HEARING_LOSS_Click:
Exit Sub

Err_HEARING_LOSS_Click:
MsgBox Err.Description
Resume Exit_HEARING_LOSS_Click

End Sub

Thanks for your help
Brook

from date -- to date -- start date

Three prompts?

Post you SQL statement.

I have a problem I have been trying to work out with several queries that
have the following date parameter so people can open the query within the

[quoted text clipped - 9 lines]

Brook

 

[Brook Morris via AccessMonster.com]

Karl

Here is the SQL info you requested

Thanks Brook

SELECT [injury mishap DBt11].DATE, [injury mishap DBt11].[Office Symbol],
[injury mishap DBt11].[Last Name B], [injury mishap DBt11].[Hearing Loss 6]
FROM [injury mishap DBt11]
WHERE ((([injury mishap DBt11].DATE)>=[forms]![REPORTING PARAMETERS]!
[txtDateFrom] And ([injury mishap DBt11].DATE)<=[forms]![REPORTING PARAMETERS]
![txtDateTo]) AND (([injury mishap DBt11].[Hearing Loss 6])=-1));

Post the SQL form your query stDocName.
Open the query in design view, click on menu VIEW - SQL View, hightlight
all, copy, and paste in a post.

Karl,

[quoted text clipped - 32 lines]

 

[Guest]

Do not see anything that would cause multiple prompts to occur in this SQL.

Do you get the multiple prompts when you run this query without using the
code?

Karl

Here is the SQL info you requested

Thanks Brook

SELECT [injury mishap DBt11].DATE, [injury mishap DBt11].[Office Symbol],
[injury mishap DBt11].[Last Name B], [injury mishap DBt11].[Hearing Loss 6]
FROM [injury mishap DBt11]
WHERE ((([injury mishap DBt11].DATE)>=[forms]![REPORTING PARAMETERS]!
[txtDateFrom] And ([injury mishap DBt11].DATE)<=[forms]![REPORTING PARAMETERS]
![txtDateTo]) AND (([injury mishap DBt11].[Hearing Loss 6])=-1));

Post the SQL form your query stDocName.
Open the query in design view, click on menu VIEW - SQL View, hightlight
all, copy, and paste in a post.

Karl,

[quoted text clipped - 32 lines]

Brook

 

[Brook Morris via AccessMonster.com]

Karl,

That is what has me stumped! I don't get them when running without the code.
However, given the number of reports needed to be run daily/weekly/monthly
for commanders and directors the specialists need to be able to use that date
discrimination. Am I making sense? LOL

I have two other questionsfor you as well:

Q1: I got tired of the usual hassle of using the microsoft date and time
picker control V6.0 on my forms. Kept getting the error mgs which I know you
are aware of.

I went today with the Datepicker.MDB and have found I must be having a brain
lapse on something. The instructions state I should be able to copy and
paste the icon directly onto my form and change the following from
=CalendarFor([InvoiceDate],"Select the Invoice Date") to =CalendarFor([DOM],
"Select the Date of Mishap") in the on click procedure for the icon.

RESULT: Get this error message:
1st Line~~"The expression on click you entered as the event property setting
produced the following error: Type Mismatch"
2d Line~~"The expression may not reult in the name of a macro, the name of a
user defined function, or [Event Procedure].
3d Line~~"There may have been an error evaluating the function, event or
macro.

SQL Code referenced:
Option Compare Database
Option Explicit

'Calendar form variable:
Public gtxtCalTarget As TextBox 'Text box to return the date from the
calendar to.

Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
On Error GoTo Err_Handler
'Purpose: Open the calendar form, identifying the text box to return
the date to.
'Arguments: txt = the text box to return the date to.
' strTitle = the caption for the calendar form (passed in
OpenArgs).

Set gtxtCalTarget = txt
DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
"CalendarFor()"
Resume Exit_Handler
End Function


Q2: My database is split into the front and backend. I have never noted any
sys type items in the table section of the front end before. I could have
sworn they were only in the backend of my DB for security.

Sorry for keeping you so busy with me today.
Brook

Do not see anything that would cause multiple prompts to occur in this SQL.

Do you get the multiple prompts when you run this query without using the
code?

Karl

[quoted text clipped - 18 lines]

 

[Guest]

I am sorry I can not help you.

I do not use code except that behind my switchboard and a module for
multi-select. I use switchboard and macros.

Karl,

That is what has me stumped! I don't get them when running without the code.
However, given the number of reports needed to be run daily/weekly/monthly
for commanders and directors the specialists need to be able to use that date
discrimination. Am I making sense? LOL

I have two other questionsfor you as well:

Q1: I got tired of the usual hassle of using the microsoft date and time
picker control V6.0 on my forms. Kept getting the error mgs which I know you
are aware of.

I went today with the Datepicker.MDB and have found I must be having a brain
lapse on something. The instructions state I should be able to copy and
paste the icon directly onto my form and change the following from
=CalendarFor([InvoiceDate],"Select the Invoice Date") to =CalendarFor([DOM],
"Select the Date of Mishap") in the on click procedure for the icon.

RESULT: Get this error message:
1st Line~~"The expression on click you entered as the event property setting
produced the following error: Type Mismatch"
2d Line~~"The expression may not reult in the name of a macro, the name of a
user defined function, or [Event Procedure].
3d Line~~"There may have been an error evaluating the function, event or
macro.

SQL Code referenced:
Option Compare Database
Option Explicit

'Calendar form variable:
Public gtxtCalTarget As TextBox 'Text box to return the date from the
calendar to.

Public Function CalendarFor(txt As TextBox, Optional strTitle As String)
On Error GoTo Err_Handler
'Purpose: Open the calendar form, identifying the text box to return
the date to.
'Arguments: txt = the text box to return the date to.
' strTitle = the caption for the calendar form (passed in
OpenArgs).

Set gtxtCalTarget = txt
DoCmd.OpenForm "frmCalendar", windowmode:=acDialog, OpenArgs:=strTitle

Exit_Handler:
Exit Function

Err_Handler:
MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation,
"CalendarFor()"
Resume Exit_Handler
End Function


Q2: My database is split into the front and backend. I have never noted any
sys type items in the table section of the front end before. I could have
sworn they were only in the backend of my DB for security.

Sorry for keeping you so busy with me today.
Brook

Do not see anything that would cause multiple prompts to occur in this SQL.

Do you get the multiple prompts when you run this query without using the
code?

Karl

[quoted text clipped - 18 lines]

Brook