Using a dialog form to set criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've set up a dialog form with 5 fields, client, dept, description, date
from, and date to. In the query where I want to set these 5 criteria I've
entered as [Forms]![Movements Dialog]![Client] and the same for the other 4.
On the 2 criteria for dates I'm using >= on the date from and <= on the Date
to. Its working well until I try to use the * in the dept and in the
description criteria as Like "*[Forms]![Movements Dialog]![Dept]]*". The
user, when entering the criteria in the dialog form for description, would
enter "loan" to see the records like personal loans or loan settlements and
would also enter the date range. It only works correctly if I don't use the
"*" the dept and description criteria entered matches the record exactly.
I'm using Access 2002-3 and the correct ANSI standard. I don't know how to
code and limited to the wizards in access and the macros. Can anyone please
help
 
Try concatenating the wildcards with the deparment string:
Like "*" & [Forms]![Movements Dialog]![Dept] & "*"

If by ANSI standard, you mean that you checked the box under:
Tools | Options | Tables/Queries | SQL Server Compatible
then use % as the wildcard instead of *.
 
Thanks for that but it still doesn't work. If in the dialog form dept field
and description field i enter a criteria that I know matches a particular
record exactly and then in the query criteria I don't use the wildcard it
works. Its the use of the wildcard thats bugging me. The use of the
wildcard is imperative in order for the whole project to be productive and
meaningful. Thanks very much for your time

Ofer said:
Try this

Like "*" & [Forms]![Movements Dialog]![Dept]] & "*"

--
\\// Live Long and Prosper \\//
BS"D


ipswichman said:
I've set up a dialog form with 5 fields, client, dept, description, date
from, and date to. In the query where I want to set these 5 criteria I've
entered as [Forms]![Movements Dialog]![Client] and the same for the other 4.
On the 2 criteria for dates I'm using >= on the date from and <= on the Date
to. Its working well until I try to use the * in the dept and in the
description criteria as Like "*[Forms]![Movements Dialog]![Dept]]*". The
user, when entering the criteria in the dialog form for description, would
enter "loan" to see the records like personal loans or loan settlements and
would also enter the date range. It only works correctly if I don't use the
"*" the dept and description criteria entered matches the record exactly.
I'm using Access 2002-3 and the correct ANSI standard. I don't know how to
code and limited to the wizards in access and the macros. Can anyone please
help
Click to expand...
Click to expand...
 
Can you post the SQL?

--
\\// Live Long and Prosper \\//
BS"D


ipswichman said:
Thanks for that but it still doesn't work. If in the dialog form dept field
and description field i enter a criteria that I know matches a particular
record exactly and then in the query criteria I don't use the wildcard it
works. Its the use of the wildcard thats bugging me. The use of the
wildcard is imperative in order for the whole project to be productive and
meaningful. Thanks very much for your time

Ofer said:
Try this

Like "*" & [Forms]![Movements Dialog]![Dept]] & "*"

--
\\// Live Long and Prosper \\//
BS"D


ipswichman said:
I've set up a dialog form with 5 fields, client, dept, description, date
from, and date to. In the query where I want to set these 5 criteria I've
entered as [Forms]![Movements Dialog]![Client] and the same for the other 4.
On the 2 criteria for dates I'm using >= on the date from and <= on the Date
to. Its working well until I try to use the * in the dept and in the
description criteria as Like "*[Forms]![Movements Dialog]![Dept]]*". The
user, when entering the criteria in the dialog form for description, would
enter "loan" to see the records like personal loans or loan settlements and
would also enter the date range. It only works correctly if I don't use the
"*" the dept and description criteria entered matches the record exactly.
I'm using Access 2002-3 and the correct ANSI standard. I don't know how to
code and limited to the wizards in access and the macros. Can anyone please
help
Click to expand...
Click to expand...
Click to expand...
 
Thanks after double checking all the related forms etc your answer solved
the problem. Thanks again.

Ofer said:
Try this

Like "*" & [Forms]![Movements Dialog]![Dept]] & "*"

--
\\// Live Long and Prosper \\//
BS"D


ipswichman said:
I've set up a dialog form with 5 fields, client, dept, description, date
from, and date to. In the query where I want to set these 5 criteria I've
entered as [Forms]![Movements Dialog]![Client] and the same for the other 4.
On the 2 criteria for dates I'm using >= on the date from and <= on the Date
to. Its working well until I try to use the * in the dept and in the
description criteria as Like "*[Forms]![Movements Dialog]![Dept]]*". The
user, when entering the criteria in the dialog form for description, would
enter "loan" to see the records like personal loans or loan settlements and
would also enter the date range. It only works correctly if I don't use the
"*" the dept and description criteria entered matches the record exactly.
I'm using Access 2002-3 and the correct ANSI standard. I don't know how to
code and limited to the wizards in access and the macros. Can anyone please
help
Click to expand...
Click to expand...
 
Thanks very much for that. Everything now works fine.

Allen Browne said:
Try concatenating the wildcards with the deparment string:
Like "*" & [Forms]![Movements Dialog]![Dept] & "*"

If by ANSI standard, you mean that you checked the box under:
Tools | Options | Tables/Queries | SQL Server Compatible
then use % as the wildcard instead of *.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

ipswichman said:
I've set up a dialog form with 5 fields, client, dept, description, date
from, and date to. In the query where I want to set these 5 criteria
I've
entered as [Forms]![Movements Dialog]![Client] and the same for the other
4.
On the 2 criteria for dates I'm using >= on the date from and <= on the
Date
to. Its working well until I try to use the * in the dept and in the
description criteria as Like "*[Forms]![Movements Dialog]![Dept]]*". The
user, when entering the criteria in the dialog form for description, would
enter "loan" to see the records like personal loans or loan settlements
and
would also enter the date range. It only works correctly if I don't use
the
"*" the dept and description criteria entered matches the record exactly.
I'm using Access 2002-3 and the correct ANSI standard. I don't know how
to
code and limited to the wizards in access and the macros. Can anyone
please
help
Click to expand...
Click to expand...
 
Back
Top