problem with parameter query

[grantschneider]

I use a form to query my card sale table. when i use the VP and SVP
parameters, it works fine. but it does weird things and gives a very
strange output when i use the EA and SC fields. With the SC and EA
fields it gives some records with EA and SC in them and others that do
not, but it does not give all the records.

here is my code:

PARAMETERS [Forms]![Card Form]![SVP] Short, [Forms]![Card Form]![VP]
Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]![Card
Form]![EA] Text ( 255 );
SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type], IIf([card type]="Renewal",[# of
Cards]*[Points]*0.5,[# of Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS
[SVP Com], [TCOM]*0.75 AS [VP Com], [Card Sale List].EA, IIf([EA] Is
Not Null,Forms![Card Form]!EAAdd,0) AS [EA Com], [Card Sale List].SC,
IIf(([Card Sale List].SC Is Not Null) And ([Card Type]="Add-on" Or
"New Sale"),[TCOM]*0.15,0) AS [SC Com]
FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft=[Card Points].Aircraft
WHERE ((([Card Sale List].SVP) Is Null Or ([Card Sale List].SVP) Like
Forms![Card Form]!SVP & "*") And (([Card Sale List].VP) Is Null Or
([Card Sale List].VP) Like Forms![Card Form]!VP & "*") And (([Card
Sale List].EA) Is Null Or ([Card Sale List].EA) Like Forms![Card Form]!
EA & "*") And (([Card Sale List].SC) Is Null Or ([Card Sale List].SC)
Like Forms![Card Form]!SC & "*"));

 

[Allen Browne]

There is a bug in Access. If you declare the parameters of type Text, and
they refer to a control on a form, JET fails to recognise them as Null.

It might be hard to believe it could get something to basic wrong, but
here's further detail and a demonstration:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

Workarounds are to either omit declaring the parameters, or to test them for
a zero-length string instead.

 

[grantschneider]

I deleted the parameters and I still have the same problem. Should I
try testing for a zero-lenght string or could it be something else?

There is a bug in Access. If you declare the parameters of type Text, and
they refer to a control on a form, JET fails to recognise them as Null.

It might be hard to believe it could get something to basic wrong, but
here's further detail and a demonstration:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

Workarounds are to either omit declaring the parameters, or to test them for
a zero-length string instead.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I use a form to query my card sale table. when i use the VP and SVP
parameters, it works fine. but it does weird things and gives a very
strange output when i use the EA and SC fields. With the SC and EA
fields it gives some records with EA and SC in them and others that do
not, but it does not give all the records.

here is my code:

PARAMETERS [Forms]![Card Form]![SVP] Short, [Forms]![Card Form]![VP]
Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]![Card
Form]![EA] Text ( 255 );
SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type], IIf([card type]="Renewal",[# of
Cards]*[Points]*0.5,[# of Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS
[SVP Com], [TCOM]*0.75 AS [VP Com], [Card Sale List].EA, IIf([EA] Is
Not Null,Forms![Card Form]!EAAdd,0) AS [EA Com], [Card Sale List].SC,
IIf(([Card Sale List].SC Is Not Null) And ([Card Type]="Add-on" Or
"New Sale"),[TCOM]*0.15,0) AS [SC Com]
FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft=[Card Points].Aircraft
WHERE ((([Card Sale List].SVP) Is Null Or ([Card Sale List].SVP) Like
Forms![Card Form]!SVP & "*") And (([Card Sale List].VP) Is Null Or
([Card Sale List].VP) Like Forms![Card Form]!VP & "*") And (([Card
Sale List].EA) Is Null Or ([Card Sale List].EA) Like Forms![Card Form]!
EA & "*") And (([Card Sale List].SC) Is Null Or ([Card Sale List].SC)
Like Forms![Card Form]!SC & "*"));- Hide quoted text -

- Show quoted text -

 

[Allen Browne]

Okay, it's a little hard to know what's going on. For example, if the form
is a bound form, you may need to save the record before it shows up in the
query. Also, you need to tab out of the text box where you made the last
entry, so that control gets updated and the query can find the value.

I've tried to look at your query; this is pure aircode, but you could try
it:

PARAMETERS [Forms]![Card Form]![SVP] Short,
SELECT [Card Sale List].SVP,
[Card Sale List].VP,
[Card Sale List].[# of Cards],
[Card Sale List].Aircraft,
[Card Sale List].[Card Product],
[Card Sale List].[Card Type],
IIf([card type]="Renewal",[# of Cards] * [Points] * 0.5,
[# of Cards] * [Points]) AS TCOM,
[TCOM] * 0.25 AS [SVP Com],
[TCOM] * 0.75 AS [VP Com],
[Card Sale List].EA,
IIf([EA] Is Null, 0, [Forms]![Card Form]![EAAdd]) AS [EA Com],
[Card Sale List].SC,
IIf(([Card Sale List].SC Is Not Null)
And ([Card Type] IN ("Add-on", "New Sale")),
[TCOM] * 0.15, 0) AS [SC Com]
FROM [Card Sale List] INNER JOIN [Card Points]
ON [Card Sale List].Aircraft = [Card Points].Aircraft
WHERE (([Card Sale List].SVP Is Null
Or [Card Sale List].SVP Like [Forms]![Card Form]![SVP] & "*")
And ([Card Sale List].VP Is Null
Or [Card Sale List].VP Like [Forms]![Card Form]![VP] & "*")
And ([Card Sale List].EA Is Null
Or [Card Sale List].EA Like [Forms]![Card Form]![EA] & "*")
And ([Card Sale List].SC Is Null
Or [Card Sale List].SC Like [Forms]![Card Form]![SC] & "*"));

Beyond that, you could open the Immediate window (Ctrl+G), and ask Access
what's going on, e.g.:
? [Forms]![Card Form]![SC]

Finally, if you want a completely different approach, this might be more
efficient:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I deleted the parameters and I still have the same problem. Should I
try testing for a zero-lenght string or could it be something else?

There is a bug in Access. If you declare the parameters of type Text, and
they refer to a control on a form, JET fails to recognise them as Null.

It might be hard to believe it could get something to basic wrong, but
here's further detail and a demonstration:
Parameter of type Text is evaluated wrongly
at:
http://allenbrowne.com/bug-13.html

Workarounds are to either omit declaring the parameters, or to test them
for
a zero-length string instead.

I use a form to query my card sale table. when i use the VP and SVP
parameters, it works fine. but it does weird things and gives a very
strange output when i use the EA and SC fields. With the SC and EA
fields it gives some records with EA and SC in them and others that do
not, but it does not give all the records.

here is my code:

PARAMETERS [Forms]![Card Form]![SVP] Short, [Forms]![Card Form]![VP]
Text ( 255 ), [Forms]![Card Form]![SC] Text ( 255 ), [Forms]![Card
Form]![EA] Text ( 255 );
SELECT [Card Sale List].SVP, [Card Sale List].VP, [Card Sale List].[#
of Cards], [Card Sale List].Aircraft, [Card Sale List].[Card Product],
[Card Sale List].[Card Type], IIf([card type]="Renewal",[# of
Cards]*[Points]*0.5,[# of Cards]*[Points]) AS TCOM, [TCOM]*0.25 AS
[SVP Com], [TCOM]*0.75 AS [VP Com], [Card Sale List].EA, IIf([EA] Is
Not Null,Forms![Card Form]!EAAdd,0) AS [EA Com], [Card Sale List].SC,
IIf(([Card Sale List].SC Is Not Null) And ([Card Type]="Add-on" Or
"New Sale"),[TCOM]*0.15,0) AS [SC Com]
FROM [Card Sale List] INNER JOIN [Card Points] ON [Card Sale
List].Aircraft=[Card Points].Aircraft
WHERE ((([Card Sale List].SVP) Is Null Or ([Card Sale List].SVP) Like
Forms![Card Form]!SVP & "*") And (([Card Sale List].VP) Is Null Or
([Card Sale List].VP) Like Forms![Card Form]!VP & "*") And (([Card
Sale List].EA) Is Null Or ([Card Sale List].EA) Like Forms![Card Form]!
EA & "*") And (([Card Sale List].SC) Is Null Or ([Card Sale List].SC)
Like Forms![Card Form]!SC & "*"));