Date Type Cast before exclude query

[samjesse]

Hi

I have tableA and tableB. I need to get the records in tableA which does not have a match in tableB. The match is found in AutoNumber in tableA and a Text type field in tableB.

How can I cast Text into Long Integer and then write the query?

many thx

 

[nanscombe]

Hi,

I have attached a zipped example database to illustrate what I'm about to say.

First of all I create a query, called qryTableB_withNumericIdForA (queryB for short), which holds all of the fields from tableB but converts a text ID field to Long by using the val() function. I append an empty string ("") to the field to ensure there is something in there to work with, it will in fact return 0 if it empty.

Secondly I created a query, called qryResult_RecordsInTableA_withoutMatchInTableB, which links tableA to queryB with an outer join (basically returns all of the records from tableA regardless of a match with tableB) and then use a criteria to show the records where the ID field in the queryB returns 0, ie there is no matching ID.

I hope this is useful.

 

[samjesse]

Hi,

I have attached a zipped example database to illustrate what I'm about to say.

First of all I create a query, called qryTableB_withNumericIdForA (queryB for short), which holds all of the fields from tableB but converts a text ID field to Long by using the val() function. I append an empty string ("") to the field to ensure there is something in there to work with, it will in fact return 0 if it empty.

Secondly I created a query, called qryResult_RecordsInTableA_withoutMatchInTableB, which links tableA to queryB with an outer join (basically returns all of the records from tableA regardless of a match with tableB) and then use a criteria to show the records where the ID field in the queryB returns 0, ie there is no matching ID.

I hope this is useful.

Thank you.
I am sorry I forgot to say that I am using Access 2000.

 

[nanscombe]

As before ... but in Access 2000 format.

 

[samjesse]

As before ... but in Access 2000 format.

Thank you.
How can I get the query to ask for start and end dates to limit the output records when run?

 

[nanscombe]

You'll need to add another criteria to the output query, on the date field in question, of something like ...

BETWEEN [Enter start date] AND [Enter end date]

That should then give you the two prompts of "Enter start date" and "Enter end date"