Fussy Query

[Dumbfounded]

Hi Guys,

I'm at a loss. I have built a purchase order system at work so that we can reduce the amount of paperwork we have as the order pads were in triplicate and we don't need that any longer.

The order number is generated automatically, the Suppliers are on a drop down menu and the purchase codes are also on a drop down. There's a control button to add new suppliers which closes the purchase order form and opens that screen and then returns to the PO screen when this has been completed and closed.

I have built a report from a query which reproduces the purchase order with the relevant info on paper if we need to print it and it requests the order number when the prompted. However, I have two suppliers that this query simply does not like. All the details are there in the table, etc. but as soon as I try to print out any orders for either of these two suppliers, no information is showing on the report which is obviously not showing in the query either.

Has anyone any idea why this is happening? It's not a complicated database at all and has no coding in it.

Thanks in advance.

Donna

 

[SOS]

By chance do these suppliers have a single quote in their name and have you used anything in your query that uses a single quote for delimiting?

 

[Dumbfounded]

Hi SOS,

No nothing untoward there - just a normal Supplier Name like J A Bloggs. The query has nothing in it other than in the criteria for the order number "Enter Order Number".

I've even tried deleting these two particular suppliers and adding them in again so they have a completely new ID number but it's sussed that it's the same suppliers and kicked them out.

I know it'll probably be something really simply or glitchy but I'm just not getting it at all and I'm scratching my head on this one.

Thanks,

Donna

 

[SOS]

Any chance you can post the database so we can take a look to see?

 

[Dumbfounded]

It would be after the weekend and I'm not sure if the Company would let me post it with any other Company information which is what we need to show why it's not working with two specific companies, but I'll pose the question.

Ta

Donna

 

[just.a.guy]

If this is access (.mdb), have you tried compressing the database?

 

[Dumbfounded]

No I haven't but it's worth a try - I'll try that first and see if it makes any difference.

Thanks

Donna

 

[Galaxiom]

It sounds like you can see the Order in the form but not in the report. They have different queries to generate them and there must be a difference in the structure of those queries.

I imagine the Orders table has a field like SupplierID that is used to join to the primary key in Supplier table which holds the supplier details.

The primary key of the Orders table would also be used as the foreign key in a table of the order lines.

In turn the order lines would include a field with the ItemID from another table of Items with their details.

Records in the Item table would include a SupplierID field to control what was available to be selected in the dropdown on the Order form.

So there are two possible indirect relationship paths from Order to Item either via OrderLine or via Supplier. If an Item was reallocated to another supplier after an original order was generated it could break a query which included joins using both of these paths.

If the relationships have been set up in the database, new queries will default to including those relationships.