Date_Dol

[vljones]

I am either having an extended blond moment or have been looking at this too long. This is a basic question but I simply cannot get my head around the solution.

I have a Search form with grouped radio buttons. Depending on the selection a different query will run. The problem I am incurring is when I am querying more than one table.

As an example:

SELECT tbl_Claim.Txt_I nsuredName, tbl_Claim.Date_ DOL, tbl_Claim.Mem_P olNo, tbl_Diary.Num_C laimNo, tbl_Diary.Date_ FUpDate, tbl_Diary.Txt_D escription, tbl_Diary.Num_D ocNo, tbl_Diary.Date_ Final, tbl_Diary.Txt_R ecieved
FROM tbl_Diary INNER JOIN tbl_Claim ON tbl_Diary.Num_C laimNo = tbl_Claim.Num_C laimNo
WHERE (((tbl_Claim.Da te_DOL) Between [Enter the Start Date] And [Enter the Finish Date]) AND ((tbl_Claim.Num _ClaimNo)=[tbl_Diary]![Num_ClaimNo]));

I have set the parameter on tbl_Diary!Date_ DOLas this is the only place this piece of information lives, yet the query needs to link back to tbl_Claim to return information that does not exist in tbl_Diary.

When I run from the query window I receive two prompts - one to enter the start date and one to enter the end date (exactly as it should). The problem is when I try & run this in code.

Exactly I have a search form with a group of radio buttons, in this instance if the user selection search by "DOL" then an OpenForm command occurs with the filtering set to this query.

When running from the search form however, I get an additional parameter. It specifically states tbl_Claim!Date_ DOL and is then followed by the parameters I have actually set.

What I need to know is how do I prevent this tbl_Claim!Date_ DOL parameter?

Cheers in advance
VJ

[JConsulting]

I am either having an extended blond moment or have been looking at this too long. This is a basic question but I simply cannot get my head around the solution.

I have a Search form with grouped radio buttons. Depending on the selection a different query will run. The problem I am incurring is when I am querying more than one table.

As an example:

SELECT tbl_Claim.Txt_I nsuredName, tbl_Claim.Date_ DOL, tbl_Claim.Mem_P olNo, tbl_Diary.Num_C laimNo, tbl_Diary.Date_ FUpDate, tbl_Diary.Txt_D escription, tbl_Diary.Num_D ocNo, tbl_Diary.Date_ Final, tbl_Diary.Txt_R ecieved
FROM tbl_Diary INNER JOIN tbl_Claim ON tbl_Diary.Num_C laimNo = tbl_Claim.Num_C laimNo
WHERE (((tbl_Claim.Da te_DOL) Between [Enter the Start Date] And [Enter the Finish Date]) AND ((tbl_Claim.Num _ClaimNo)=[tbl_Diary]![Num_ClaimNo]));

I have set the parameter on tbl_Diary!Date_ DOLas this is the only place this piece of information lives, yet the query needs to link back to tbl_Claim to return information that does not exist in tbl_Diary.

When I run from the query window I receive two prompts - one to enter the start date and one to enter the end date (exactly as it should). The problem is when I try & run this in code.

Exactly I have a search form with a group of radio buttons, in this instance if the user selection search by "DOL" then an OpenForm command occurs with the filtering set to this query.

When running from the search form however, I get an additional parameter. It specifically states tbl_Claim!Date_ DOL and is then followed by the parameters I have actually set.

What I need to know is how do I prevent this tbl_Claim!Date_ DOL parameter?

Cheers in advance
VJ

Hi VJ,
I'm kind of having a Blonde Moment reading through this.

We need to know how you're running your query. You mention that you can run it from the query list. That's fine. But you say it doesn't run from the search form.

Do you have it coded? Can you show us that?

sSQL = "Select * from table1 inner join table2.id = table1.id where somefield = '" me.somefield & "';"

currentdb.execu te sSQL

or does your search form simply select that particular query based on the checkbox?

Little confused how you're using the checks, and what exactly should happen when you run the query.

[vljones]

Hi

Ok - I have a search form which contains only radio buttons. 1 = ClaimNo, 2 = Name, 3 = DOL and 4 = PolicyNo.

I have a separate query set up on each search response. have used one only as an example as the problem is ocurring on all, but once one is fixed they are all fixed, by application of same philosophy they should also work.

So_

1. Two tables named tbl_Claim and tbl_Diary. Both tables are linked by a field called Num_ClaimNo.

2. frm_DiarySearch = unbound form contain a group of radio buttons. Depending on radio button selection, when click on search cmd button, the appropriate query is called to retrieve the relevant data for the search.

3. The queries are parameter queries, the a parameter set on the field within the query depending on what the search string is. In this case tbl_Claim!Date_ DOL.

4. For DOL the query is as posted earlier. Whatver the selection when the user clicks on the search cmd button in the search form it performs the following:

Private Sub cmd_Search_Clic k()
On Error GoTo Err_cmd_Search_ Click

If Forms!frm_Diary Search!Response = 1 Then
Call ClaimNoSQL

ElseIf Forms!frm_Diary Search!Response = 2 Then
Call InsuredSQL

ElseIf Forms!frm_Diary Search!Response = 3 Then
Call PolNoSQL

ElseIf Forms!frm_Diary Search!Response = 4 Then
Call DOLSQL
End If


Exit_cmd_Search _Click:
Exit Sub

Err_cmd_Search_ Click:
MsgBox Err.Description
Resume Exit_cmd_Search _Click


This is supposed to Open a form and use the query results to populate the form.

The particular piece of code for the DOL search is as follows:

Sub DOLSQL()

DoCmd.OpenForm "frm_DiarySearc hResults", , "qry_DiaryD OL", , acFormReadOnly
DoCmd.Close acForm, "frm_DiarySearc h"

End Sub

Run through query window no extra input boxes are displayed other than thos specifically identified in the query. When run from the search button it displays an extra parameter inputbox requesting tbl_Claim.Num_C laimNo. I can Ok or cancel this to display the parameters I have requested, however, due to the first parameter input box the query run through the form does nto display the resullts.

I am tring to query two tables. The first displays data on the search string the seconds brings in the additional informationfrom table 1 where linked by Num_ClaimNo.

THis it SOOOOOO hard to explain. Dangerous but +61 2 413305130 if easier to explain and resolve over the phone. Fear not you O/S peoples, I am a computer geek, henceforth a night owl!


Cheers
VJ

[vljones]

Congrats to anyone who interpreted that!!! Sorry not sure of terminology etc so please bear with me....



NOVICE AT WORK!!!

[vljones]

Sorry I sit here thinking about how porrly i addressed the situation:

1. Form with radio buttons and cmd_Search button. (frm_DiarySearc h)
2. On click of search button if/else - dependant on selection (see previous post).
3. Search Results to be displayed in frmDiarySearcvR esults.
4. When clivking on cmd button in form additonal parameter displayed (doesnt not display when running from query window). Additional parameter is usually asking for input on field that links the two tables.

Suspect I have wrong fields in query or am not setting parameters correctly.

Just want to OpenForm "frm_DiarySearc hResults" with the parameters set in the query without having the additional parameter prompt for a response.

Sorry sorry sorrythis is so basic and I m so blond in both coding and requesting assistance it seems :)

VJ

[JConsulting]

Sorry I sit here thinking about how porrly i addressed the situation:

1. Form with radio buttons and cmd_Search button. (frm_DiarySearc h)
2. On click of search button if/else - dependant on selection (see previous post).
3. Search Results to be displayed in frmDiarySearcvR esults.
4. When clivking on cmd button in form additonal parameter displayed (doesnt not display when running from query window). Additional parameter is usually asking for input on field that links the two tables.

Suspect I have wrong fields in query or am not setting parameters correctly.

Just want to OpenForm "frm_DiarySearc hResults" with the parameters set in the query without having the additional parameter prompt for a response.

Sorry sorry sorrythis is so basic and I m so blond in both coding and requesting assistance it seems :)

VJ

I'm sure its much better in person :)

I'm looking at your query, seeing as though that's where the problem seems to be stemming from..

SELECT tbl_Claim.Txt_I nsuredName, tbl_Claim.Date_ DOL, tbl_Claim.Mem_P olNo, tbl_Diary.Num_C laimNo, tbl_Diary.Date_ FUpDate, tbl_Diary.Txt_D escription, tbl_Diary.Num_D ocNo, tbl_Diary.Date_ Final, tbl_Diary.Txt_R ecieved
FROM tbl_Diary INNER JOIN tbl_Claim ON tbl_Diary.Num_C laimNo = tbl_Claim.Num_C laimNo
WHERE (((tbl_Claim.Da te_DOL) Between [Enter the Start Date] And [Enter the Finish Date]) AND ((tbl_Claim.Num _ClaimNo)=[tbl_Diary]![Num_ClaimNo]));

the last bit where you are using

[tbl_Diary]![NumClaimNo]

try changing the ! to a .

Seems simple, but it could make a world of difference. Now, if the query has parameters outside of the one that you're showing us, then we would need to see those.

Let me read through the rest and I'll add more if I find anything.
J

[JConsulting]

Sorry I sit here thinking about how porrly i addressed the situation:

1. Form with radio buttons and cmd_Search button. (frm_DiarySearc h)
2. On click of search button if/else - dependant on selection (see previous post).
3. Search Results to be displayed in frmDiarySearcvR esults.
4. When clivking on cmd button in form additonal parameter displayed (doesnt not display when running from query window). Additional parameter is usually asking for input on field that links the two tables.

Suspect I have wrong fields in query or am not setting parameters correctly.

Just want to OpenForm "frm_DiarySearc hResults" with the parameters set in the query without having the additional parameter prompt for a response.

Sorry sorry sorrythis is so basic and I m so blond in both coding and requesting assistance it seems :)

VJ

In your command string

DoCmd.OpenForm "frm_DiarySearc hResults", , "qry_DiaryD OL", , acFormReadOnly


remove the "Qry_DiaryD OL". IF that's meant to be the recordsource for the form, then that's not applied correctly. That's where criteria would go. Example
"[myfield] = 0"

Give that a go and hopefully that clears up the mystery.
J