Prompt For Information, Access 2000 & 2003

[jfcby]

Hello,

Could someone explain how the Prompt For Information works with a
query?

I cannot get it to work.

Thank you,
jfcby

 

[Douglas J. Steele]

In the graphical query builder, you'd put something like [Enter Desired
Colour] in the Criteria row under a field. When you run the query, a pop-up
box, looking similar to the InputBox, should appear, with a title of "Enter
Parameter Value", and a prompt of Enter Desired Colour.

 

[SusanV]

Instead of putting the value for the field in the WHERE clause, you enclose
your message to the user in square brackets, and the input box value is used
for the WHERE clause:

SELECT YourField FROM YourTable WHERE YourField =[Message you want to show
in the popup box, like Enter Employee ID or somesuch]

 

[Guest]

Hi

Almost everyone in this forum will know how to "prompt" users for
information and many different circumstances. But like most things the
better you understand the process the simpler you will find it to get the
results you want.

There are many sites that will give hints on how to prompt but the best I
have seen for the novice user (from your post regarding querries) is at

http://office.microsoft.com/training/training.aspx?AssetID=RC010969991033

have a look and see what you think

Good luck

 

[jfcby]

In the graphical query builder, you'd put something like [Enter Desired
Colour] in the Criteria row under a field. When you run the query, a pop-up
box, looking similar to the InputBox, should appear, with a title of "Enter
Parameter Value", and a prompt of Enter Desired Colour.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)

Hello,

Could someone explain how the Prompt For Information works with a
query?

I cannot get it to work.

Thank you,
jfcby- Hide quoted text -

- Show quoted text -

Hello Douglas,

Your code and advice worked great but I was able to get the Promt For
Information to work. But I have this problem now with the Prompt For
Information:
My query looks like this:

Part Number Price Part Desc
AA 2.99 EBOOK-A
FA 3.00 EBOOK-A
BA 2.45 EBOOK-A
CA 1.99 EBOOK-A

My Price field is trimed
Price: Trim([Price $])

and formated in the Criteria field like so
Like (Format("#.##"))

I cannot get my Prompt For Information to list the 2.99 or 3.00. It
will list all the data at one time.

How can I get this Prompt For Information to work?
Like (Format("#.##") & [Enter Building Number or Leave Blank For All]
& "*")

Thank you for your help,
jfcby

 

[Douglas J. Steele]

What's the data type of Price? If it's a numeric field, does your query
actually use the Format function, or are you simply setting the Format
property for the field?

 

[jfcby]

What's the data type of Price? If it's a numeric field, does your query
actually use the Format function, or are you simply setting the Format
property for the field?

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Hello Douglas,

Your code and advice worked great but I was able to get the Promt For
Information to work. But I have this problem now with the Prompt For
Information:
My query looks like this:

Part Number Price Part Desc
AA 2.99 EBOOK-A
FA 3.00 EBOOK-A
BA 2.45 EBOOK-A
CA 1.99 EBOOK-A

My Price field is trimed
Price: Trim([Price $])

and formated in the Criteria field like so
Like (Format("#.##"))

I cannot get my Prompt For Information to list the 2.99 or 3.00. It
will list all the data at one time.

How can I get this Prompt For Information to work?
Like (Format("#.##") & [Enter Building Number or Leave Blank For All]
& "*")

Thank you for your help,
jfcby- Hide quoted text -

- Show quoted text -

Hello Doug,

My Data Type is numeric. Some of my data is 2.500 and 3.650. I do not
want that data to show only the data with this format 2.00 and 3.00.

 

[Douglas J. Steele]

Hello Douglas,

Your code and advice worked great but I was able to get the Promt For
Information to work. But I have this problem now with the Prompt For
Information:
My query looks like this:

Part Number Price Part Desc
AA 2.99 EBOOK-A
FA 3.00 EBOOK-A
BA 2.45 EBOOK-A
CA 1.99 EBOOK-A

My Price field is trimed
Price: Trim([Price $])

and formated in the Criteria field like so
Like (Format("#.##"))

I cannot get my Prompt For Information to list the 2.99 or 3.00. It
will list all the data at one time.

How can I get this Prompt For Information to work?
Like (Format("#.##") & [Enter Building Number or Leave Blank For All]
& "*")

Thank you for your help,
jfcby- Hide quoted text -

- Show quoted text -

Hello Doug,

My Data Type is numeric. Some of my data is 2.500 and 3.650. I do not
want that data to show only the data with this format 2.00 and 3.00.

I don't think you're going to be able to use a prompt like you're trying,
then. Just because you've formatted the data to 2 decimal points doesn't
change the data to only have two decimal points. It may show as 2.00, but
actually be 1.99997 or 2.0001. As well, you can't use Like with numeric
values: only with Text.

Try something like the following as your Criteria:

BETWEEN [Enter Value] - 0.005 AND [Enter Value] + 0.005

Make sure you've got exactlyt the same text in both places. If you don't,
you'll get prompt twice. You may have to play with the 0.005 value.

 

[jfcby]

Hello Douglas,
Your code and advice worked great but I was able to get the Promt For
Information to work. But I have this problem now with the Prompt For
Information:
My query looks like this:
Part Number Price Part Desc
AA 2.99 EBOOK-A
FA 3.00 EBOOK-A
BA 2.45 EBOOK-A
CA 1.99 EBOOK-A
My Price field is trimed
Price: Trim([Price $])
and formated in the Criteria field like so
Like (Format("#.##"))
I cannot get my Prompt For Information to list the 2.99 or 3.00. It
will list all the data at one time.
How can I get this Prompt For Information to work?
Like (Format("#.##") & [Enter Building Number or Leave Blank For All]
& "*")
Thank you for your help,
jfcby- Hide quoted text -
- Show quoted text -

Hello Doug,

My Data Type is numeric. Some of my data is 2.500 and 3.650. I do not
want that data to show only the data with this format 2.00 and 3.00.

I don't think you're going to be able to use a prompt like you're trying,
then. Just because you've formatted the data to 2 decimal points doesn't
change the data to only have two decimal points. It may show as 2.00, but
actually be 1.99997 or 2.0001. As well, you can't use Like with numeric
values: only with Text.

Try something like the following as your Criteria:

BETWEEN [Enter Value] - 0.005 AND [Enter Value] + 0.005

Make sure you've got exactlyt the same text in both places. If you don't,
you'll get prompt twice. You may have to play with the 0.005 value.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- Show quoted text -

Hello Doug,

Thank you for the code it worked great but not exactly the way I need
it to.

Since my Price $ Field is Trim() I was able to put Price $ in another
field and use Like "#.##" (removed the format) to display only that
number type. Then I used the Like [Enter Data] & "*" to display all
data if no data typed or only the data typed.

Is there a way to use both of these together Like "#.##" and Like
[Enter Data] & "*" in the same field?

Your code would not disply all data but would display only the data
typed in. I need to be able to display all data as described above.

Thank you for your help,
jfcby

 

[Douglas J. Steele]

On Feb 3, 6:39 am, "Douglas J. Steele"

Hello Doug,

Thank you for the code it worked great but not exactly the way I need
it to.

Since my Price $ Field is Trim() I was able to put Price $ in another
field and use Like "#.##" (removed the format) to display only that
number type. Then I used the Like [Enter Data] & "*" to display all
data if no data typed or only the data typed.

Is there a way to use both of these together Like "#.##" and Like
[Enter Data] & "*" in the same field?

Your code would not disply all data but would display only the data
typed in. I need to be able to display all data as described above.

Try going into the SQL of the query and using

WHERE (MyField Like "#.##" Or MyField Like [Enter Data] & "*")

 

[jfcby]

On Feb 3, 6:39 am, "Douglas J. Steele"

Hello Doug,

Thank you for the code it worked great but not exactly the way I need
it to.

Since my Price $ Field is Trim() I was able to put Price $ in another
field and use Like "#.##" (removed the format) to display only that
number type. Then I used the Like [Enter Data] & "*" to display all
data if no data typed or only the data typed.

Is there a way to use both of these together Like "#.##" and Like
[Enter Data] & "*" in the same field?

Your code would not disply all data but would display only the data
typed in. I need to be able to display all data as described above.

Try going into the SQL of the query and using

WHERE (MyField Like "#.##" Or MyField Like [Enter Data] & "*")

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- Show quoted text -

Hello Doug,

I modified your code for it to work the way I needed it to:

Like "#.##" And Like [Enter Data] Or Like [Enter Data] & "*"

Thank you for all your help,
jfcby

 

[jfcby]

Instead of putting the value for the field in the WHERE clause, you enclose
your message to the user in square brackets, and the input box value is used
for the WHERE clause:

SELECT YourField FROM YourTable WHERE YourField =[Message you want to show
in the popup box, like Enter Employee ID or somesuch]

--
hth,
SusanV

Hello,

Could someone explain how the Prompt For Information works with a
query?

I cannot get it to work.

Thank you,
jfcby- Hide quoted text -

- Show quoted text -

Hello Susan,

Thank you for your help.

jfcby

 

[jfcby]

Hi

Almost everyone in this forum will know how to "prompt" users for
information and many different circumstances. But like most things the
better you understand the process the simpler you will find it to get the
results you want.

There are many sites that will give hints on how to prompt but the best I
have seen for the novice user (from your post regarding querries) is at

http://office.microsoft.com/training/training.aspx?AssetID=RC01096999...

have a look and see what you think

Good luck
--
Wayne
Manchester, England.








- Show quoted text -

Hello Wayne,

Thank you for your help

jfcby