Form not working with query

  • Thread starter Thread starter grantschneider
  • Start date Start date
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);
 
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.
 
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
 
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
 
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...
 
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
 
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))
 
Back
Top