Parameter Problem

[AccessTexas]

I have a query that we put the po number in manually in design view each time. Because the linked table is text the entry is like this ("53847"). If we parameter it so it prompts for the po number it just sits there. I've tried making it a string in SQL but I haven't had any luck.

This is probably pretty basic but I'm going in the wrong direction and wonder if there is a quick solution. I've tried looking in the archives but found nothing yet that covers this specifically.

 

[gromit]

Hi -

You can pass a parameter to a query by entering a prompt in the design view. E.g. where you normally put the PO number, put the following (including the brackets).

[Enter PO]

When you open the query, you should get a message box that says "Enter PO". This should work with either a number or text.

You could also create a form and launch the PO from a command button. Create a form with a text box to contain the PO number, then use the form wizard to make a button to launch the query.

Post back if you still have questions.

- g

 

[raskew]

Folllowing on to Gromit's suggestion:

When in query design view, from the toolbar click on Query/Parameters:
specify [Enter PO] and select Text as type.

Bob

 

[AccessTexas]

Parameter

I've tried the parameter before and I tried it again - [Enter PO Number] and using Text as type but it alway hangs up and the "Program doesn't respond comes up".

Is it possible to do it in SQL where you change it to a string? I've tried it but I'm not sure I have the syntax right.

Also, the table is linked and it does say that it is text.

 

[AccessTexas]

Thanks for your reply. I still haven't got it solved. I think the linked table which says it is text still isn't recognized by the parameter. I've tried Str() and a host of different things but to no avail.

 

[gromit]

Sorry that it is still giving you problems.

See attached for a simple example. Probably something is different between this and yours, however, otherwise you would have been able to get this to work already.

The SQL statement looks like this:

SELECT tblPurchaseOrders.PurchaseOrder, tblPurchaseOrders.Date, tblPurchaseOrders.Amount, tblVendors.VendorName
FROM tblPurchaseOrders INNER JOIN tblVendors ON tblPurchaseOrders.VendorID = tblVendors.VendorID
WHERE (((tblPurchaseOrders.PurchaseOrder)=[Purchase Order]));

It's the WHERE clause at the end that causes the parameter input.

If this still doesn't work, can you post a stripped down version of the table and query?

- g

 

[AccessTexas]

My SQL looks like this when it works
WHERE (((informix_po_hdr.poh_nbr)="53874") AND (([informix_po_ln]![pol_nbr]=[informix_po_ln_desc]![pol_id]) Like -1))
ORDER BY informix_po_ln.pol_vitem_cd;

When I put in [What is your PO Number] is when it bogs down.

I'm new to most of this but if I sent a stripped down version would it work because most of the data is from a link?

 

[gromit]

See if you can duplicate the problem in a small stripped down db (if necessary you may need to include a stripped down version of the linked table).

If you can't duplicate the problem in an example db, then it probably means that there is something specific to your configuration.

I think that I am running out of ideas, but I'll check back in case you do post again.

 

[AccessTexas]

I made a duplicate .mdb and instead of linking, I imported. I then stripped out most of the 10's of thousands of records so I could put out the stripped out version. Well when it was large it didn't work but now that it is small it does. There are 4 tables joined that create a huge amount of duplicate records within this. I think that the size of this query might be the problem so thanks for all your help and maybe reply if you think I'm way off the track.