Queries not returning new data

[MattWhiteley]

Simple version: Any new data I add to my database isn't showing up when I run a query.

Longer version: I've noticed it on 2 queries. One which is my ChargerSearch query with the field name:

ChargerSearch: "Charger ID: "+[Charger_ID]+"      "+"Scheme Contract: "+[Scheme_Contract]+"      "+"Nissan Serial Number: "+[Post_Serial_Number]+"      "+"Client: "+[Client]+"      "+"Site: "+[Site]

and the Criteria:

Like "*" & [Enter Search Term] & "*"

And my JobHistory Query which has multiple fields from many tables. With criteria only on the Charger_ID field "[Enter Charger ID]". Both work fine for data that was already there but any new data I've added isn't returning any results when I enter the correct values in the parameters. The data is definitely there in the appropriate tables, it just won't find it on the queries.

Any help?

 

[JHB]

Simple version: Any new data I add to my database isn't showing up when I run a query.

When does it then show up?
After you've closed the form/database and open it again or when?

 

[GohDiamond]

Open your Query in SQL View, Copy the SQL Statement and post it here so that we can have a look at it.

Cheers,
Goh

 

[vbaInet]

ChargerSearch: "Charger ID: "+[Charger_ID]+"      "+"Scheme Contract: "+[Scheme_Contract]+"      "+"Nissan Serial Number: "+[Post_Serial_Number]+"      "+"Client: "+[Client]+"      "+"Site: "+[Site]

The plus (+) sign you're using to concatenate the strings is used for Null Propagation. That is to say, if any of your fields is Null, the entire string will return Null. Change + to &.

 

[MattWhiteley]

The plus (+) sign you're using to concatenate the strings is used for Null Propagation. That is to say, if any of your fields is Null, the entire string will return Null. Change + to &.

Thanks, this worked for my Charger Search query but not my JobHistory query

In response to the other replies: It doesn't (or didn't at all, in the case of the charger search) show up at all in the queries, even after closing the database down and reopening it. The data is there in the tables but the query isn't pulling up the information.

Here's the SQL code for the JobHistory Query:

SELECT Jobs.Charger_ID, Jobs.Job_Type, Jobs.SAV_No, Jobs.Date_Reported, Visit.Error, Visit.Description_of_Fault, Visit.Other_Comments, Visit.Planned_Date, Visit.Visit_Date, Visit.Team_Member1, Visit.Team_Member2, Visit.Description_of_Work_Carried_Out, Visit.[Job Status], Visit.Additional_Comments, Visit.Duration, Visit.Parts_Required, Visit.Parts_Required.Value, Visit.Parts_Used, Visit.Parts_Used.Value, Visit.Billed_Month, Visit.Billed_Year, Visit.Billed_Value, Asset.Scheme_Contract, Asset.Product_Type, Asset.Post_Serial_Number, Asset.DBT_Serial_Number, Asset.Client, Asset.Site, Asset.Address, Asset.Postcode
FROM ((Asset INNER JOIN Jobs ON Asset.Charger_ID = Jobs.Charger_ID) INNER JOIN StatusHistory ON Asset.Charger_ID = StatusHistory.ChargerID) INNER JOIN Visit ON Jobs.Database_Job_ID = Visit.JobID
WHERE (((Jobs.Charger_ID)=[Enter Charger ID]));

EDIT: I did some googling and found that changing INNER JOIN to LEFT JOIN can solve the problem (I've not the foggiest idea what that actually means), I did this and instead of getting not returning the results it's returning them twice. New code below.

SELECT Jobs.Charger_ID, Jobs.Job_Type, Jobs.SAV_No, Jobs.Date_Reported, Visit.Error, Visit.Description_of_Fault, Visit.Other_Comments, Visit.Planned_Date, Visit.Visit_Date, Visit.Team_Member1, Visit.Team_Member2, Visit.Description_of_Work_Carried_Out, Visit.[Job Status], Visit.Additional_Comments, Visit.Duration, Visit.Parts_Required, Visit.Parts_Required.Value, Visit.Parts_Used, Visit.Parts_Used.Value, Visit.Billed_Month, Visit.Billed_Year, Visit.Billed_Value, Asset.Scheme_Contract, Asset.Product_Type, Asset.Post_Serial_Number, Asset.DBT_Serial_Number, Asset.Client, Asset.Site, Asset.Address, Asset.Postcode
FROM ((Asset INNER JOIN Jobs ON Asset.Charger_ID=Jobs.Charger_ID) LEFT JOIN StatusHistory ON Asset.Charger_ID=StatusHistory.ChargerID) LEFT JOIN Visit ON Jobs.Database_Job_ID=Visit.JobID
WHERE (((Jobs.Charger_ID)=[Enter Charger ID]));

 

[vbaInet]

Change the joins manually, right-click the join and select the appropriate option.

 

[MattWhiteley]

And what would the appropriate option be? I've attached an image of the design view of the query that's duplicating results at the moment

 

[vbaInet]

I was advising based on what you thought was the solution. What problems are you having with Job history? And is it also a concatenated string?

 

[MattWhiteley]

I didn't really have any idea what the solution was, I changed INNER JOIN to LEFT JOIN on the basis of something I read elsewhere, I don't know what the difference between the two is.

The problem now seems to be that it is duplicating results. Essentially the query works by the user entering a charger ID (asset table) as the parameter and it then looks up all the jobs (jobs table) that have been done on that charger and all the visits (visits table) within those jobs (there can be multiple visits to a job, and multiple jobs can/will be done to an asset over time). Originally when I was entering a charger ID to a newly added job (i.e. after having created the query) it wasn't returning those results, now it is returning them but it's returning them twice.

I hope there is enough information there for you to advise what the solution may be. Thanks in advance.

 

[vbaInet]

Is this supposed to be a read-only form? Or are you expecting to use this form for data entry?

If it's the former then review the fields and see if there are any fields that you don't need included in the results then remove them. Review the tables and remove any tables that you haven't used. After all this, if you're still getting duplicate records, select Yes in the Unique Values property in the Property Sheet of the query.

If it's the latter, then you will need to break that query into smaller chunks and use subforms to display related records.

 

[MattWhiteley]

It's the former, there actually is a table that isn't being used in there (it was initially and then I realised that it didn't actually provide any useful information), I'm going to assume that that will be the problem?

Edit: Yeah it seems to have done the trick. Many thanks.

 

[vbaInet]

No it shouldn't be a problem because it's not being used.