Searchbox based off a query

[jjake]

Simply reverse the process - you know the date portion so remove that then you have your record ID back?

So just have the user search by using the last 1, 2 or 3 digits instead of the whole number?

If so I wouldn't mind this but it would be simpler in my opinion on the user to be able to enter 11061511234 Instead of 11234 because it wouldn't require thinking. Or if they wanted to pull up all records within the 110615 range?

 

[Minty]

The user can enter the original number - you process that and maybe give them the option you have suggested to show all records for that date or just the record entered.
I really would recommend a separator character - _ in the number you choose to display to assist you with splitting it back out later. Don't use a space!

 

[jdraw]

Perhaps they could enter the ProductName and get the number behind the scene, and use if for searching.

This sort of thing fits under the heading of interface design.
What would you like to do to set up a search in plain English? Forget how you currently have it that seems a little confusing/difficult. If you can describe what you would like to happen, I'm sure readers will respond with some options.

As I hinted earlier, your choice of concatenated things may be contributing to the issue.

Good luck with your project.

 

[jjake]

Perhaps they could enter the ProductName and get the number behind the scene, and use if for searching.

This sort of thing fits under the heading of interface design.
What would you like to do to set up a search in plain English? Forget how you currently have it that seems a little confusing/difficult. If you can describe what you would like to happen, I'm sure readers will respond with some options.

As I hinted earlier, your choice of concatenated things may be contributing to the issue.

Good luck with your project.

New purchase order - > Purchase order # generated using (Current mmddyy+OrderID). e.g. 11061501 (November 6th 2015 + record 01) > Enter information vendor, purchase etc> Click submit button > PO Generated on report.

Search form > Text box > Enter 11061501 > Click Search> Finds Purchase order form with purchase order # 11061501.

Seems pretty straight forward but the concatenating to get my PO# seems to be the issue here...

Do I need to generate the PO# another way so it can be stored and easy to find later??

 

[jdraw]

There must be a customer or supplier associated with the Purchase Order. Instead of making the user remember date and number, could you not get the Purchase orders associated with Porky Pig Inc ( in 2015 or whatever else) and list the Purchase OrderIds.
Click on 1 and it opens the PO in a new form. If it's the wrong one, cancel and go back to the list.

As for the coding/concatenation --- why not PODate (date/Time) as the PurchaseOrderDate, an some unique number (could be autonumber) as the POId.

If you give us a little more context (tables involved) maybe there will be more focused answers.

What exactly is the purpose/objective of the search?

 

[jjake]

See attached. It will be easier to explain.

 

[jdraw]

OK, I've looked at your database quickly.
I don't use macros, prefer vba.
I would not use lookups at the table field level.
I would use explicit tables and relationships (traditional lookup tables)
I would identify relationships in the relationship window.
Suggest you should only use alphanumerics (and underscore(_) in field and object names.

I have adjusted relationships as per jpg.

 

[jjake]

With all these changes in mind which look great, Will this help me with my original problem?

 

[jjake]

Ok so here is what I ended up doing.

I made a query with [OrderID] [PONumber] [CompanyName].

I then used a Combo box using the query as the criteria to take the information from. Then I just hid the [OrderID] and [CompanyName] by adjusting the margins.

So now when I click the Search Button it looks up the [OrderID] and brings up the form to the correct record.