Form not working with query

G

grantschneider

I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:

SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);
 
O

Ofer Cohen

What do you mean by not working?

Check each field separatly mybe the problem is with one of them.

If you have combo's selection as a filter, check if the right column is
bounded.
 
G

grantschneider

What do you mean by not working?

Check each field separatly mybe the problem is with one of them.

If you have combo's selection as a filter, check if the right column is
bounded.

--
Good Luck
BS"D



[email protected] said:
I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:
Click to expand...
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

When I run the query, i get no results back. that is what i mean about
nor working
 
G

grantschneider

What do you mean by not working?
Click to expand...
Check each field separatly mybe the problem is with one of them.
Click to expand...
If you have combo's selection as a filter, check if the right column is
bounded.
Click to expand...
[email protected] said:
I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);- Hide quoted text -
Click to expand...
Click to expand...
- Show quoted text -
Click to expand...

When I run the query, i get no results back. that is what i mean about
nor working- Hide quoted text -

- Show quoted text -
Click to expand...

It only works if I leave the SVP field blank
 
O

Ofer Cohen

Open the form, select a value in the Combo93, Open the Immidiate window
(press Ctrl+G) and type

?[Forms]![Share Comm]![Combo93]

Press enter and see what value returned, is that what you expected to be
returned to use as a filter

--
Good Luck
BS"D


[email protected] said:
What do you mean by not working?
Click to expand...
Check each field separatly mybe the problem is with one of them.
Click to expand...
If you have combo's selection as a filter, check if the right column is
bounded.
Click to expand...
:
I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:
Click to expand...
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);- Hide quoted text -
Click to expand...
- Show quoted text -
Click to expand...

When I run the query, i get no results back. that is what i mean about
nor working- Hide quoted text -

- Show quoted text -
Click to expand...

It only works if I leave the SVP field blank
Click to expand...
 
G

grantschneider

Open the form, select a value in the Combo93, Open the Immidiate window
(press Ctrl+G) and type

?[Forms]![Share Comm]![Combo93]

Press enter and see what value returned, is that what you expected to be
returned to use as a filter

--
Good Luck
BS"D



[email protected] said:
On Jul 27, 1:26 pm, Ofer Cohen <[email protected]>
wrote:
What do you mean by not working?
Check each field separatly mybe the problem is with one of them.
If you have combo's selection as a filter, check if the right column is
bounded.
--
Good Luck
BS"D
:
I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);- Hide quoted text -
- Show quoted text -
When I run the query, i get no results back. that is what i mean about
nor working- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
It only works if I leave the SVP field blank- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

It returns the value that I have selected within the field. So if its
blank, it gives me "Null" and if I have an SVP in the field it gives
me that SVP.

I think there may be a problem with the VBA code even though I have
not touched it.

Here it is:
Private Sub SVP_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![SVP], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
 
G

grantschneider

Open the form, select a value in the Combo93, Open the Immidiate window
(press Ctrl+G) and type

?[Forms]![Share Comm]![Combo93]

Press enter and see what value returned, is that what you expected to be
returned to use as a filter

--
Good Luck
BS"D



[email protected] said:
On Jul 27, 1:26 pm, Ofer Cohen <[email protected]>
wrote:
What do you mean by not working?
Check each field separatly mybe the problem is with one of them.
If you have combo's selection as a filter, check if the right column is
bounded.
--
Good Luck
BS"D
:
I have created a form where the SVP field is a record selector and the
other fields are just used to change the parameters of the query. They
dont seem to work however. Here is my SQL:
SELECT [Master Sales Forecast].Account, [Master Sales
Forecast].Aircraft, BEShare.SVP, IIf([Master Sales Forecast].SVP Is
Null,0,([svpa]*forms![share comm]!svpa+[svpg]*forms![share comm]!svpg+
[svpn]*forms![share comm]!svpn+[svpr]*forms![share comm]!svpr-
[vpcom])*forms![share comm]!svpt) AS SVPCom, BEShare.VP, IIf([Master
Sales Forecast].VP Is Null,0,([vpa]*forms![share comm]!vpa+[vpg]*forms!
[share comm]!vpg+[vpn]*forms![share comm]!vpn+[vpr]*forms![share comm]!
vpr)*forms![share comm]!vpt*forms![share comm]!vps) AS VPCom,
BEShare.SC, IIf([Master Sales Forecast].SC Is Null,0,([sca]*forms!
[share comm]!sca+[scg]*forms![share comm]!scg+[scn]*forms![share comm]!
scn+[scr]*forms![share comm]!scr)*forms![share comm]!SCT*forms![share
comm]!sct) AS SCCom, BEShare.EA
FROM [Master Sales Forecast] INNER JOIN BEShare ON [Master Sales
Forecast].ID = BEShare.ID
WHERE ([BEShare].SVP = [Forms]![Share Comm]![Combo93]
OR [Forms]![Share Comm]![Combo93] IS NULL)
AND ([BEShare].VP = [Forms]![Share Comm]![VP]
OR [Forms]![Share Comm]![VP] IS NULL)
AND ([BEShare].EA = [Forms]![Share Comm]![EA]
OR [Forms]![Share Comm]![EA] IS NULL)
AND ([BEShare].SC = [Forms]![Share Comm]![SC]
OR [Forms]![Share Comm]![SC] IS NULL);- Hide quoted text -
- Show quoted text -
When I run the query, i get no results back. that is what i mean about
nor working- Hide quoted text -
- Show quoted text -
Click to expand...
Click to expand...
It only works if I leave the SVP field blank- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

It sends me to the debugger and highlights the following line:

rs.FindFirst "[ID] = " & Str(Nz(Me![SVP], 0))
 

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

problem with forms and queries 2
form not working with query 0
Finding matching records on a form 0
Problem with forms/parameter query 2
problem with parameter query 3
Querying multple criteria but allowing them to not be blank 3

Top