MS Access SQL query

%3CLINGO-SUB%20id%3D%22lingo-sub-1365353%22%20slang%3D%22en-US%22%3EMS%20Access%20SQL%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365353%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EI%20am%20not%20getting%20result%20from%20the%20below%20query.%3C%2FP%3E%3CP%3Ecan%20u%20please%20help%20to%20short%20out%20the%20problem%20of%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESELECT%20%5BNPS%20ATM%5D.%5BTrnxn%20Mode%5D%2C%20%5BNPS%20ATM%5D.%5BTrnxn%20Type%5D%2C%20%5BNPS%20ATM%5D.%5BCard%20Number%5D%2C%20%5BNPS%20ATM%5D.RRN%2C%20%5BNPS%20ATM%5D.STAN%2C%20%5BNPS%20ATM%5D.%5BApp%20Code%5D%2C%20%5BNPS%20ATM%5D.Amount%2C%20%5BNPS%20ATM%5D.%5BTerminal%20ID%5D%2C%20%5BNPS%20ATM%5D.JOIN%3CBR%20%2F%3EFROM%20%5BNPS%20ATM%5D%3C%2FP%3E%3CP%3EINNER%20JOIN%20%5BNPS%20ATM%20REV%5D%20ON%20%5BNPS%20ATM%5D.%5BJOIN%5D%20%3D%20%5BNPS%20ATM%20REV%5D.%5BJOIN%5D%3CBR%20%2F%3EWHERE%20(((%5BNPS%20ATM%20REV%5D.JOIN)%20Is%20Null))%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBr%2C%3C%2FP%3E%3CP%3ESuprio%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1365353%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1365711%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20SQL%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1365711%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F619828%22%20target%3D%22_blank%22%3E%40suprio_sarker%3C%2FA%3E%26nbsp%3BWhat%20DOES%20the%20query%20return%3F%20An%20Error%3F%20Nothing%3F%20The%20wrong%20values%3F%20Too%20many%20records%3F%20Too%20few%20records%3F%26nbsp%3B%3C%2FP%3E%3CP%3EW%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1366061%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20SQL%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1366061%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIts%20return%20Nothing.%3CBR%20%2F%3E%3CBR%20%2F%3EBr%2C%3CBR%20%2F%3ESuprio%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1399382%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20SQL%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1399382%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F46682%22%20target%3D%22_blank%22%3E%40George%20Hepworth%3C%2FA%3E%20%2C%3CBR%20%2F%3EThat%20means%20if%20we%20use%20CONCATENATE%20to%20join%20to%20different%20data%20and%20use%20this%20value%20in%20query%20that%20does%20not%20give%20result.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1369482%22%20slang%3D%22en-US%22%3ERe%3A%20MS%20Access%20SQL%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1369482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F619828%22%20target%3D%22_blank%22%3E%40suprio_sarker%3C%2FA%3E%26nbsp%3BYour%20queries%20are%20parameterized%20so%20they%20return%20only%20subsets%20of%20the%20total%20number%20of%20records%20in%20the%20linked%20Excel%20spreadsheet.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20query%20called%20%22NPS%20ATM%20Without%20Matching%20NPS%20ATM%20REV%22%20seems%20to%20be%20the%20one%20from%20which%20your%20posted%20SQL%20is%20taken.%20It%20has%20as%20a%20parameter%20%22Join%20Is%20Null%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20turns%20out%20that%20there%20are%20NO%20records%20that%20match%20that%20parameter.%20In%20other%20words%20ALL%20fields%20called%20%5BJoin%5D%20in%20both%20tables%20do%20have%20matching%20records.%20That%20means%20this%20SQL%20returns%20no%20records%20because%20%5BJoin%5D%20is%20never%20Null.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

 

Hello,

I am not getting result from the below query.

can u please help to short out the problem of it. 

 

 

SELECT [NPS ATM].[Trnxn Mode], [NPS ATM].[Trnxn Type], [NPS ATM].[Card Number], [NPS ATM].RRN, [NPS ATM].STAN, [NPS ATM].[App Code], [NPS ATM].Amount, [NPS ATM].[Terminal ID], [NPS ATM].JOIN
FROM [NPS ATM]

INNER JOIN [NPS ATM REV] ON [NPS ATM].[JOIN] = [NPS ATM REV].[JOIN]
WHERE ((([NPS ATM REV].JOIN) Is Null));

 

 

Br,

Suprio

 

5 Replies
Highlighted

@suprio_sarker What DOES the query return? An Error? Nothing? The wrong values? Too many records? Too few records? 

W

Highlighted
@George Hepworth,

Its return Nothing.

Br,
Suprio
Highlighted

@suprio_sarker Your queries are parameterized so they return only subsets of the total number of records in the linked Excel spreadsheet. 

The query called "NPS ATM Without Matching NPS ATM REV" seems to be the one from which your posted SQL is taken. It has as a parameter "Join Is Null".

 

It turns out that there are NO records that match that parameter. In other words ALL fields called [Join] in both tables do have matching records. That means this SQL returns no records because [Join] is never Null. 

Highlighted
@George Hepworth ,
That means if we use CONCATENATE to join to different data and use this value in query that does not give result.
Highlighted

@suprio_sarker I don't know what you mean by "CONCATENATE". I see nothing in this SQL to indicate you are concatenating something. Please explain further. (I realize English may not be your primary language, so it can be harder to get the point across.)