Date formatting and Efficiency

[InstructionWhich7142]

i asked this last week:

http://www.access-programmers.co.uk/forums/showthread.php?t=188857

after a lot of searches and thinking i managed to write the below which almost achieves the aims, i have two small problems still:


first is that the line:
DoCmd.RunSQL "update orderstab Set deldate = #" & oldestdeldate & "# where speckey = '" & specordline & "';"
(10ish lines from the end of the code)
it writes the date value stored in "oldestdeldate" [stored as a date] as a text value instead (does the field it writes too need to be formatted as a date to fix this? or should access correct this if i can get the SQL to write it back correctly?)

the second is speed, looping through 2000 records takes a long time (10 mins?) what can i do to streamline this code?

is there a more elegant way to loop than calling it from a function over and over? also should i clear the variables at the end of every loop?

Option Compare Database
Option Explicit

Function matchlinescall()
Do
If DCount("speckey", "orderstab", "sumofqty > 0") <> 0 Then
    Call lineref
    Else
    Exit Function
End If
Loop
End Function


Sub lineref()
'#no confirmations
DoCmd.SetWarnings False


'# Order line number as a key
Dim linerefvar As String

'# earliest date of orders table
Dim oldestreqdate As Date
'# specific line of orders table
Dim specordline As String

'# oldest delivery date
Dim oldestdeldate As Date
'# specific line on deliveries table
Dim specdelline As String

'# quantity that was delivered
Dim specdelqty As Integer
'# quantity that was ordered on that line
Dim specordqty As Integer
'# product of the two
Dim qtyresult As Integer
'# not negative result
Dim notnegresult As Integer



'# po & line to work on:
linerefvar = DLookup("key", "orderstab", "sumofqty > 0")
'MsgBox linerefvar

'# schedule line to work on:
oldestreqdate = DLookup("min([reqdate])", "orderstab", "key = '" & linerefvar & "' And sumofqty > 0")
'MsgBox oldestreqdate

specordline = DLookup("speckey", "orderstab", "key = '" & linerefvar & "' And reqdate = #" & Format(oldestreqdate, "mm/dd/yyyy") & "#")
'MsgBox specordline

'# delivery line to work on:
If (DCount("speckey", "delstab", "key = '" & linerefvar & "' And sumofbqty > 0")) = 0 Then
    DoCmd.RunSQL "update orderstab Set deldate = 'not delivered'  where speckey = '" & specordline & "';"
    DoCmd.RunSQL "update orderstab set sumofqty = 0 where speckey = '" & specordline & "';"
    Exit Sub
End If

oldestdeldate = DLookup("min([deldate])", "delstab", "key = '" & linerefvar & "' And sumofbqty > 0")
'MsgBox oldestdeldate

specdelline = DLookup("speckey", "delstab", "key = '" & linerefvar & "' And deldate = #" & Format(oldestdeldate, "mm/dd/yyyy") & "#")
'MsgBox specdelline

specdelqty = DLookup("sumofbqty", "delstab", "speckey = '" & specdelline & "'")
'MsgBox specdelqty

specordqty = DLookup("sumofqty", "orderstab", "speckey = '" & specordline & "'")
'MsgBox specordqty

qtyresult = specordqty - specdelqty
'MsgBox qtyresult

If qtyresult <= 0 Then
    DoCmd.RunSQL "update orderstab Set deldate = #" & oldestdeldate & "#  where speckey = '" & specordline & "';"
End If

notnegresult = qtyresult * -1
'MsgBox notnegresult

If qtyresult < 0 Then
        DoCmd.RunSQL "update delstab set sumofbqty = '" & notnegresult & "' where speckey = '" & specdelline & "';"
        DoCmd.RunSQL "update orderstab set sumofqty = 0 where speckey = '" & specordline & "';"
    Else
        DoCmd.RunSQL "update orderstab set sumofqty = '" & qtyresult & "' where speckey = '" & specordline & "';"
        DoCmd.RunSQL "update delstab set sumofbqty = 0 where speckey = '" & specdelline & "';"
End If
Exit Sub
'# warnings on again
DoCmd.SetWarnings True
End Sub

 

[Galaxiom]

Avoid the DLookUps. They are slow since each one is essentially a query on a recordset.
Better to get all your data into a single recordset query.

The control needs to be formatted as date.

Your code has several direct exits.
Taking any of them may cause the warnings to not be reset.
Correct practice is to have a single point of exit in a subsection of code that includes SetWarnings True.
Otherwise they remain off throughout Access.

In fact your current code looks like leaving without turning on warnings is inevitable since the line is preceeded by Exit Sub.

You also need to include Error Handling and ensure that this leaves through the same door.

 

[InstructionWhich7142]

right, i'd noticed a bit about "Recordsets" when i was working out the above, i guess i'll have to look into them more :/

The control needs to be formatted as date.

you've lost me, the source of the variable is a date, and the variable is stored as a date,

does the field it is writing to also need to be formatted as a date or do i miss understand what you mean by control?


i'll move the warnings into the initial function so i dont keep switching them on and off every loop that way it will avoid the exits all together,

error handling is the next thing on the list to fix

 

[Galaxiom]

Use the query designer to make a query that gathers all the information you need returned into one record.

You can use this query as the Record SOurce of a subform. It will open on the single record and bound textboxes on the form will show the values.

Otherwise you can use the query as the basis for a recordset in VBA and use that for the message boxes as you originally described.


The boxes on Forms and Reports are called Controls. Fields are in tables and queries.
Set the format property of your textbox control to the type of date you require.

 

[InstructionWhich7142]

ooh, i'm really sorry, the whole point of the program is that the data cannot be queried properly (bad design of the guy before me)

the schedule lines have no unique reference, the only reference is to about 5 of them at a time (the order and line of the order they are against) after that the addition of date is the only thing that makes them unique,

the program matches the lines off in the order of oldest first much like you would manually

 

[Galaxiom]

I don't understand. A query can return the same information as a Domain function but with the added advantage of multiple fields and records. An arbitrary recordset of any data can be created using subqueries and unions.

The absence of a single primary key field should not be a problem as it appears the Order and Line fields would work as a composite key. Or are you saying that multiple records share both these values?

 

[InstructionWhich7142]

The absence of a single primary key field should not be a problem as it appears the Order and Line fields would work as a composite key. Or are you saying that multiple records share both these values?

exactly

Order 001 line 001 can have 5 different quantities on 5 different dates,

i dont see how i could query in this situation?





edit: would the SQL equilivent of a Dlookup be faster? (ie swap all my dlookups for sql expressions)

 

[Galaxiom]

It is a bit hard to grasp the structure or the database but I think you might be spot on about the bad design.

Moreover I fear the problem is going to grow worse as the number of records increases. Would not be the first time someone made a mess that only became obvious after as the records increased. Then they are nowhere to be found.

The multiple records for each orderline should have been stored in another table with a one to many relationship between the lines and the filled dates.

You might be better considering restructuring than trying to work around the problems. Bloody hard though when you are trying to build on someone else's shemozle.

Can you post a relationship diagram for the current structure. Maybe it isn't as bad ias it sounds (trying to be optimistic here).

If you can combine the Domain functions that read from the same table/query into normal query then that would save some time. You really need to get a recordset based on a query which will holds all the information you need.

 

[InstructionWhich7142]

Pegasus - Operations II
its some Foxpro based application, can't play with that end unfortunately,
i'm getting the data out in ODBC


erm, not sure what a relationship diagram should look like but:

Purchase Order Header Table:
Purchase order number (key field)
Supplier name
Address,
Who placed the Order

Purchsase Order Items Table:
P.O. number (ref's back to the above table)
line number (line on the PO)
part code (item on order)
total quantity
price

Purchase Order Schedule Table:
Po Number
Line Number
Date
Qty

so you have 1 order, fine
5 lines on that order, also fine, unique refs with P.o. and Line
but 5 schedule lines against that order...

edit:
the reciepts table has the same issues

you can have 5 deliveries on 5 different dates against 1 line on 1 PO :/

 

[Galaxiom]

They should have normalised the supplier as an ID and recorded their name and address in another table but that structure looks OK as far as the original question goes.

The Schedule and presumably the Receipts would be related to the Lines by the PO Number and Line Number. (As it uses two field it is a composite key).

The main problem aside from the plethora of Domain functions appears to be that your current methodology is pulling all the records.

I haven't looked really closely but I think you can achieve you goal with subforms linked by the two fields forming the composite key.

 

[InstructionWhich7142]

the problem is if:
line item 1
of
Purchase Order 7

has 5 lines of schedules against it alone,

and also 5 deliveries,

there is no way to individually determine which schedule a delivery is made against,

there is no reference like "Schedule Line A"

to give "7.1.A" as a unique reference to that schedule line alone,

all 5 lines just have 7.1

if you see what i mean?

 

[gemma-the-husky]

i know it may be important, but does it matter particularly?

can you not just use up the oldest schedules etc, until they are all used up.

or just compare the total supply, against the total order

some of these things can get away with a "fuzzy" logic of that type.
========

 

[InstructionWhich7142]

"can you not just use up the oldest schedules etc, until they are all used up"

thats what the program does