Advanced reports

[MrDeej]

Hello!

I am somewhat stuck on this problem.

I have 1 table which contains an expiry date for products, named TBL productinfo and one table wich contains the exception in how long the product is ok'd for sale called TBL exception products.

Fields in the tables
TBL productinfo
Product number
Expiry date (number format) e.g.: 1071201 is in realitiy 01.12.2007

TBL exception products
Product number
Exception number of months (number format) e.g.: 5



The thing i need to do is that i need a report on the products which are 3 months ahead in time. Easy with just TBL productinfo. Then i use <1071201 as criteria.
Problem is that when a product is apperent in the TBL exception products with a "Exception number of months" value of 5 it should also be a part of the list even if the expiry date is 1080201. And if the criteria is 1071201 and a product has an post in TBL exception products with "exception number of months" of 1 it shouldnt come on the list before the expiry date is 1071001 even though the criteria is 1071201

In short words, i need to make a report which makes all products in TBL product info to appear on the list when it has less than 3 months to expiry date unless it has a post in TBL exception products and then should automaticly adjust the criteria to what stands in "Exception number of months"

Possible soloutions:
1. Make a sql sentence which checks each product if it has a connection to the "exception" table, where it uses the post in exception if any, but when it dosent have a exception it should use the standard 3 month

---

[MrDeej]

2. Make a query which adds up the expiry date using the "exceptions" table. Problem is that it needs to add up e.g. 5 months to a 1071101 date formatted with number format. It would be easy if it wasent for the year change that happends after 1071201. Then i have to format the string to be a date format and then add 5 or 3 or 1 or whatever months to the date format and then format it back so that i can use it as criteria..

I dont know how to do this in sql code. I may manage to do this in vba but then i dont know how to make use of this in a report.

hmmmm

[MrDeej]

Ok. I have managed to make a code that does the job (in a way) but of course this need a triple super computer to be somewhat fast.

take a look:

 Dim vd As Date
 Dim db   As DAO.Database
 Dim rst  As DAO.Recordset
 
DoCmd.RunSQL "DELETE * FROM [IBD temp expiredate]"
 
  Set db = Access.Application.CurrentDb
  Set rst = db.OpenRecordset("TBL product info", dbOpenDynaset)
 
  With rst
    .MoveLast
    .MoveFirst
 
    Do While .EOF = False
 
        pnr = !ProductNumber
        exp = !ExpiryDate
        check = DLookup("[Exception number of months]", "TBL exception products ", "Productnumber=" & vnr)
        If Not IsNull(check) Then
 
 
 
            year = "20" & Mid(exp, 2, 2)
            month = Mid(exp, 4, 2)
            date = Right(exp, 2)
 
 
            vd = Date & "." & month & "." & year
            vd2 = DateAdd("m", check, vd)
 
            newexp = "1" & Right(vd2, 2) & Mid(vd2, 4, 2) & Left(vd2, 2)
 
            Dim rst3 As New ADODB.Recordset
 
            rst3.Open "[IBD temp expiredate]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
                With rst3
                .AddNew
                ![Product number] = pnr
                ![expiredate] = newexp
                .Update
            End With
            rst3.Close
            Set rst3 = Nothing
 
        Else
 
            Dim rst4 As New ADODB.Recordset
 
            rst4.Open "[IBD temp expiredate]", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
                With rst4
                .AddNew
                ![Product number] = pnr
                ![Expiredate] = exp
                .Update
            End With
            rst4.Close
            Set rst4 = Nothing
 
        End If
 
        .MoveNext
    Loop
  End With
 
  Set rst = Nothing
  Set db = Nothing

I am embarrased topost so bad code on the forum. Please kick my in the ass and tell me a easier way to solve this case if there is any

[MrDeej]

Ok. I give it 16 hours before i implement and start using this sucy code to do the task (point of no return).

Lets hope someone come with some comments before that :)