Query Help (I think)

darkone

New member
Local time
Today, 16:44
Joined
Dec 29, 2004
Messages
8
I need a query or vb function that I can put in that will increase a number by 1 every 3 months. example below
this is a table

[FIELD1] [FIELD2]
John Doe | 3

so in 3 months time that 3 will become a 4.
I don't have a date field in my forms or tables if that matters.

Thank you
 
You could write a query that you could run every three months to increment the values by one, but I see several problems with this, the biggest one being:- how would you ensure it was run once (and only once) every three months?

Can you add a date field to your table ?

tblMain
--------
NameLast
NameFirst
BaseDate

Then you could write a query that performs a function using the BaseDate and returns the number of whole three month periods elapsed since the BaseDate.

This would allow you more precision as to when people reach the next level. e.g. if you do it using an update query run every three months, everyone "graduates" together. Using a BaseDate and function people could "graduate" on specific days.

It is a little difficult to be of any more help without understanding more about your purpose.
 
date addition

Yes i can add a date field. Basically what I need is say an individualis hired on 12-1-04 and every 3 months his tech level would go up by 1. So in turn when hired he is tech level one and in 3 months it will automatically goto 2 etc.

thank you
 
I would suggest you do add the date field.

Then create a new code module (or use an existing one if appropriate).

Create a function to determine the tech level, based on a supplied date. Here is one I've had a hack at for you; you may need to tweek it a little...

Code:
Public Function nTechlevel(ByVal varAppointed As Variant) As Integer
    'Use a Variant parameter, in case record contains Null
    On Error GoTo nTechlevel_err
    Dim nMonths As Integer
    Dim dteAppointed As Date
    
    If Not IsNull(varAppointed) Then
        If IsDate(varAppointed) Then
            dteAppointed = CDate(varAppointed)
        Else
            Err.Raise vbObjectError + 1001, "nTechLevel", "Input is not recognisable as a date."
        End If
    Else
        Err.Raise vbObjectError + 1002, "nTechLevel", "Input was null."
    End If
    
    If dteAppointed > Now() Then
        Err.Raise vbObjectError + 1003, "nTechLevel", "Future Dated appointment."
    End If
    
    nMonths = DateDiff("m", dteAppointed, Now())
    
    'reduce by incomplete quarters
    nMonths = nMonths - (nMonths Mod 3)
    
    nTechlevel = (nMonths / 3) + 1
    
nTechlevel_exit:
    Exit Function
    
nTechlevel_err:
    ' assign a value that would be easy to spot
    ' so you will know something has gone wrong
    'You can assign different values to show the cause of the error
    Select Case Err.Number
    Case (vbObjectError + 1003)
        nTechlevel = -9996 'Future Dated Appointment
    Case (vbObjectError + 1002)
        nTechlevel = -9997 'Null input
    Case (vbObjectError + 1001)
        nTechlevel = -9998 'Not a recognisable date
    Case Else
        nTechlevel = -9999 'Other error
    End Select
    'Alternateley (or also), issue an error message (de-comment the MsgBox line, below).
    'the error message on its own can make it more difficult to
    'work out which record is causing the error
    
    'MsgBox "An error has occurred." & vbCrLf & "Number = " & Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source, vbCritical + vbOKOnly, "Error"
    
End Function

Hint : I've catered for a few specific error scenarios. You could also eliminate the possibility of these by :-
a) ensuring the field type is Date/Time (will always be recognisable as a date)
b) setting the required property to True (can't be Null)
c) set a Validation Rule (+Validation Text) to ensure YourDate <= now()

If you were to do all of those things, you could use the following instead

Code:
Public Function nTechlevel(ByVal dteAppointed As Date) As Integer
    On Error GoTo nTechlevel_err
    Dim nMonths As Integer
    
    nMonths = DateDiff("m", dteAppointed, Now())
    
    'reduce by incomplete quarters
    nMonths = nMonths - (nMonths Mod 3)
    
    nTechlevel = (nMonths / 3) + 1
    
nTechlevel_exit:
    Exit Function
    
nTechlevel_err:
    ' assign a value that would be easy to spot
    ' so you will know something has gone wrong
    nTechlevel = -9999 'Other error
    
    'Alternateley (or also), issue an error message (de-comment the MsgBox line, below).
    'the error message on its own can make it more difficult to
    'work out which record is causing the error
    
    'MsgBox "An error has occurred." & vbCrLf & "Number = " & Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source, vbCritical + vbOKOnly, "Error"
    
End Function

Then call the function from a query. (put something along the following lines in the "Field" line of the design grid:-
TechLevel: nTechlevel([YourTable]![YourDateField])

HTH

Regards

John
 
Thank You!!!!!

Worked like a charm, I really appreciate it! However, I have 1 more request on that code if possible:

Is there a way to cap it? In other words I want it to go up 1 point every 3 months which it does now but I don't want to exceed 10.
Is that possible?

By the way I used the second code.

Thank you again,
:D
 
You are welcome. Glad to help.

Your extra request comes under the heading of Q.E.D. (quite easily done)!!!!

In the code, just before the line:-

nTechlevel_exit:

add in the line below:-

if nTechlevel > 10 then nTechlevel = 10

And Robert, as they say, will be your Father's Brother !
 
Again thank you!

I will try that when I get home! :D

Since your this good do you have any info on like doing a backup of data into a file (without erasing a previous backup) automatically and erasing the info that was in the database.
This way I won't have records longer than 6 months in there and it is back up from previous months in a file

Hope I am not bugging you!

Rick
 
darkone,

you also should consider creating records per employee as for their tech level.
What you do right now is overwriting the current tech level data.
This way you're not able to track and trace any historical data.

RV
 
RV,
RV said:
What you do right now is overwriting the current tech level data.
I disagree with your assertion. Nothing is being overwritten. A calculation is being performed based on the (static & stored) starting date, and the current date, and the result displayed. There is no reason DarkOne would not be able to use a "historical" TechLevel function to perform basically the same calculation using a past (as in historical) and passed (as in parameter) date, if he were to have such a need.
 
DarkOne (Rick),

I don't understand why you would want a database that would delete all of its data every six months??? That would mean no-one would ever have a techlevel > 3, yet you asked for it to be capped at 10 ??? I am failing to understand something here!

If you do want a totally clean database every 6 months, I would create a master copy to use as a template, and then rename the existing one with an appropriate back-up date name, and make a new copy from the template as and when required.

Sorry, but what I am understanding your question to be does not make sense to me.

I would suggest you might try doing a search on this forum for "archive", and getting some ideas from people who know (far) more about that subject than I do.
 
Backup

The reason I was wondering if I could do that is that way I will have data in the database for 6 months at a time but still wanting to keep a backup incase I need to go back and review it later (like an export to excel or a file) of the data that was there before. Basically like house cleaning and starting fresh again every six months but storing the data in a file incase I need to go back. Hope I exlplained it better this time

Rick
 

Users who are viewing this thread

Back
Top Bottom