Date function on database fields

ASP Free Forums Sponsor:

March 21st, 2005, 03:50 AM

Lexy

Registered User

Join Date: Mar 2005

Posts: 1

Time spent in forums: 11 m 54 sec
Reputation Power: 0

Date function on database fields

Hi Guys,

I'm writing an item subscription program for my class. The Access database records include StartDate and EndDate for subscription. I'd like to have a function to go through the record and check for persons whose subscription expires next month if today is on the 28th of this month provided today is not weekend. The function should list the firstname of the affected person on a form. Here what I got as the function

Public Function Remind() As Integer

Dim rmyr As Integer

Dim rmmon As Integer

Dim rmday As Integer

Dim cur As String

Dim dt As Integer

rmyr = Year(Now)

rmmon = Month(Now)

rmday = Day(Now)

cur = Weekday(Now)

Select Case rmmon

Case 1 Or 3 Or 4 Or 5 Or 6 Or 7 Or 8 Or 9 Or 10 Or 11 Or 12

If rmday >= 28 And cur <> vbSaturday Or cur <> vbSunday Then

frmRemind.Show

End If

Case 2

If rmday >= 25 And cur <> vbSaturday Or cur <> vbSaturday Then

frmRemind.Show

End If

End Select

End Function

Here is what I got for the form load event

Private Sub Form_Load()

Beep

Set pdbSubscription = OpenDatabase(App.Path & "\SubTrix.mdb")

query = "Select * From tblSuscription ORDER BY fldSubID"

dt = Format$(fldSubEndDate, "m")

query2 = "SELECT * FROM tblSubscription Where dt = rmmon + 1"

Set mrstRemind = pdbSubscription.OpenRecordset(query2)

Do Until mrstRemind.EOF = True

List1.AddItem mrstRemind.Fields("fldSubFirstname") & " (" & mrstRemind.Fields("[fldSubLastname]") & ")"

mrstRemind.MoveNext

Loop

End Sub

The function is called from the form load event of the main window, but is not working

I also want a function to sniff thru record and add total sales values from last Monday till today if today is Friday. The function should place the totalvalue in the field named week with the week number Here is what I got as the function

Public Function Wkovrtotal()

Dim Wktotal As Integer

Dim dte1 As Integer

Dim dte2 As Integer

Dim dte3 As Integer

Dim dte4 As Integer

Dim dte5 As Integer

Dim qry1 As Integer

Dim qry2 As Integer

Dim qry3 As Integer

Dim qry4 As Integer

Dim qry5 As Integer

query = "Select * from tblCirculation"

dte5 = DateAdd("d", -5, fldCirDate)

dte4 = DateAdd("d", -4, fldCirDate)

dte3 = DateAdd("d", -3, fldCirDate)

dte2 = DateAdd("d", -2, fldCirDate)

dte1 = DateAdd("d", -1, fldCirDate)

qry5 = "Select fldDailySalesValue from tblCirculation where dte5 = 5"

qry4 = "Select fldDailySalesValue from tblCirculation where dte4 = 4"

qry3 = "Select fldDailySalesValue from tblCirculation where dte3 = 3"

qry2 = "Select fldDailySalesValue from tblCirculation where dte2 = 2"

qry1 = "Select fldDailySalesValue from tblCirculation where dte1 = 1"

Wktotal = qry5 + qry4 + qry3 + qry2 + qry1

End Function

Where tblCirculation is table in the database while fldDailySalesValue and fldCirDate are fields there in

Here is where the function is used:

Private Sub SSTab1_Click(PreviousTab As Integer)

Text1.Text = Wktotal

End Sub

The same should actually be done for the month by adding the totals of every week in that month together and place it in a field named month with month name

i'm really new to vb..... Can someone help me on this....?

Thank you for all ur time

March 22nd, 2005, 11:01 AM

sbaxter

Moderator: Access, SQL

Join Date: Oct 2003

Posts: 5,126

Time spent in forums: 4 Days 1 h 2 m 51 sec
Reputation Power: 12

Quote:

Originally Posted by Lexy

Why waste your time with a function

create a query that generates the results you want and open the query

S-

__________________
If you have found a particular post helpful, show your appreciation by adding reputation points to that user by clicking the "scales" image in the upper right had corner of their post.

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: