query

[brett]

I hate to be a pest but I'm still getting nowhere. It
looks like a mess here but I hope you can read it. I'm at
a complete loss. There is an inputbox that the user
inputs the value (of the variable JulianDate). After the
input for the JulianDate, there is one-lined equation that
also turns it into the value for the variable
CalendarDate. Two more user inputs are having the program
decide which serial range table to use (variable is Table)
and what the division is; the contract is needed because
the same ICN number can be used multiple times (one for
each of the nine possible different contract) on the
IMAGING_FOLDER_T table. This table is what stores every
piece of data and this is where I trying to run a query
against (since I needed two fields: contract and the ICN
number). Following all that is where my loop finally
starts in. Here's a copy of the code for my the loop that
I was talking about.

'Setting the min and max ranges for the ICN loop
BeginningSerialRange = DLookup("[MIN_SERIAL_NBR]",
Table, "[CLM_RECEIVED_DT] = " & CalendarDate)
EndingSerialRange = DLookup("[MAX_SERIAL_NBR]",
Table, "[CLM_RECEIVED_DT] = " & CalendarDate)

'Loop to count ICN numbers
For Serial = BeginningSerialRange To EndingSerialRange

'Puts together the ICN number
ICNnumber = Right(JulianDate + 100000, 5) & Right
(Serial + 100000, 5)

'Queries the IMAGING_FOLDER_T table for the ICN
strSQL = "SELECT [Contact/Division].Contract,
IMAGING_FOLDER_T.ICN " _
& "FROM IMAGING_FOLDER_T INNER JOIN
([Contact/Division] INNER JOIN " _
& "IMAGING_BATCH_T ON
[Contact/Division].Process_NM =
IMAGING_BATCH_T.PROCESS_NM) " _
& "ON IMAGING_FOLDER_T.BATCH_NM =
IMAGING_BATCH_T.BATCH_NM WHERE
((([Contact/Division].Contract)" _
& "='" & Contract & "') AND
((IMAGING_FOLDER_T.ICN)='" & ICNnumber & "'));"
Set qdf = dbs.CreateQueryDef("tempcodetest", strSQL)
'Runs the query
qdf.Execute
'Writes to a table if the ICN doesn't exist
If qdf Is Null Then
Set rsttable = dbs.OpenRecordset
("UnusedICNnumbers", dbOpenTable, dbDenyRead)
With rsttable
.AddNew
!ICN = ICNnumber
.Update
.Close
End With
End If
qdf.Close
DoCmd.DeleteObject (acQuery = "tempcodetest")
Next Serial
End Function

You can see that the BeginningSerialRange and
EndingSerialRange is set by the Dlookup function querying
a table to get the beginning and ending numbers for that
day that are possible and that's where the loop starts (I
did it this way because I thought I needed like an
incrementing counter running and had to query looking for
each individual ICN number). The main reason I also
thought a query was needed was due to needing two
restrictions to see if there was a result (that's why I
don't think the Dlookup would work since I need two fields
to be true to find an ICN number). But what you said
about the IsNull is kind of what I was looking to find if
say running the query came back Null (without a record or
a result), then write that ICNnumber to the "Unused ICN
numbers" table. Does this help you see what I'm doing any
better?? I know I'm probably thinking very procedural and
I'm open to anything if it makes the code work better
because I hate the idea of creating a query temporarily
and then deleting it two seconds after (this cycle or my
loop could run around ten thousand cycles so performance
and time consumption is pretty much a big deal with this
query). I just find it very difficult to write a report
filled with numbers that aren't used that do not exist
anywhere. Thanks so much for your help so far.

Brett

I'm not fully following what your trying to tell me. The
start of the code I wrote gets some user input and the
range of numbers that I am trying to look through can vary
depending on the user inputs. Example: one time the
search could be 0319742500 to 0319779999 but another time
the search could be 0319910000 to 0319929999; these ranges
are set by having the code look at values from a total
different set of tables and these ranges can always change
depending on user selections.

Well? Parameter Queries routinely use user input. It is
not necessary
to use looping and VBA code to deal with multiple records.
You *can*,
but it's rarely the simplest or most efficient way to do
it.

Anyways, as the code goes
on, it clears this little table with one integer field in
it as you described below (so this part I was already
doing). The next part jumps into a loop starting a
counter that puts together the first number 0319910000
which builds a quick Access query to search for this
number. My next step is running the query to see if this
number exists on a different table and this is where I'm
losing it.

If you insist on doing it this way, I'd suggest not using
a Query; use
a syntax such as

If IsNull(DLookUp("[fieldname]", "tablename", "[fieldname]
= " & X)

to see if there is a record in the table with value X.

The next step is what I need help with. If
the query finds the number, I want it to continue on
through the loop and delete the query and restart the loop
at 0319910001 but if it can't find the number, I want it
to write that number to this table (you referred to as
Num) and then delete the query and restart the loop at the
next number 0319910001.

Use the AddNew method and write out your loop index to the
table.

I'm not sure if what you stated
as a recommendation to try would still work but I wanted
to clarify a little more what I am attempting to do. If
your recommendation would still work, I guess I just can't
visualize it working and just wanted some reassurance.

My recommendation can be adapted to work. It probably
won't right out
of the box because I do not have your database and don't
follow
exactly what the context is, or why you're trying to do
this. It will
require that you stop thinking procedurally and start
thinking
relationally, though; but since you're using a relational
database,
this should be a very valuable learning experience if you
choose to
make the effort!


..

 

[John Vinson]

I hate to be a pest but I'm still getting nowhere.

Brett,

Did you even TRY the query? It WILL work. I believe that the reason
you're having difficulty is that you are mindlocked into doing it one
record at a time. Relational databases DO NOT WORK one record at a
time - they work in sets.

I'll try to find time between paying work and 2nd quarter taxes to
look over your long post... but I'd like to know if you seriously
considered my prior suggestion.