Query for obtaining numbers between 2 fields

[GoldenOrb]

Hi, Im relatively new to Access. Ive got a handle on the basics but need some help.

Im trying to create a database for mailing specific addresses in a street.

What fields I have is a {Field 1}Start_Street_number (ie House 10) and a {field 2}End_Street_number (ie House 20)... Street Name, Street type (which is its on query options) and Suburb (which is its on query options).

What result I am chasing is entering the Start_ Street_number and End_Street_number to have access generate all the numbers in including and between.

In the above mentioned example... Start_Street_number (ie House 10) and a End_Street_number (ie House 20)
Enter house 10, Enter house 20... Smith Street..... Apple Valley

To obtain result of
10 Smith Street Apple Valley... 11 Smith Street Apple Valley.. and so on up until 20 Smith Street Apple Valley.

Does anyone have any hints on how to achieve this please.

Thanks in advance
Geoff

[June7]

Here's a basic of queries that you missed: a query can't retrieve records that don't exist. You want to create records using the street number fields as parameters. A VBA procedure could write records to a 'temp' table. This would involve a recordset, loop structure, and sql INSERT action. Something like:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tablename WHERE your criteria here;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
CurrentDb.Execute "DELETE FROM temptablename"
For i = rs!Start_Street_Number to rs!End_Street_Number
CurrentDb.Execute "INSERT INTO temptablename(HouseNumber, StreetName, StreetType, Suburb) VALUES(" & i & ", " & rs!StreetName & ", " & rs!StreetType & ", " & rs!Suburb & ")"
Next

This method requires setting a VBA reference to: Microsoft ActiveX Data Objects 2.8 Library

[GoldenOrb]

Cool thanks, Ive been meaning to get back to this forum but just after I posted a few days later my hard drive died on me with all my links.

Ill give this a shot, thanks again

[June7]

I was assuming that Start_Street_number and End_Street_number fields have only numeric values, no text.

[GoldenOrb]

Yeah thanks thats fine... Stupid thing is the most the houses in this dust bowl dont have letter boxes anyway due to all the mail theft, so all Im really doing is trying to appease the bosses in something that wont probably work or be received... well until next months crazy idea comes up. Ill give that a go tonight.

Thanks