Calculating a query to get same results as in my form

  • Thread starter Thread starter Kass W
  • Start date Start date
K

Kass W

In my form, I have automatically calculated Age (from
entered data of Date of Birth); a special "User Name"
using the UCase function with Last Name & First Name (ex:
DOEJOHN); and automatically fill in Zip Code for area
towns using the DLookUp function to a table I made with
area towns and their respective zip codes (I don't need
all the zip codes, just from around our area). I am also
using some Combo Boxes for some info, but wanted the above
items to caluclate automatically if possible. The form
works great and calculates just as I wanted, saving time
and possible error of entry! However you know the
calculated info does not appear in the corresponding table
for me to query from. I understand I can calculate in a
query, but I don't quite get how. I am needing to query
out Patron Information that will include the usual Name,
Address, Phone info., including the Age, User Name, and
Zip Code that my form caluculated for me. I understand
using Queries to select certain info, but don't quite
grasp how to calculate the info. like my form did.

Thanks to anyone who can help me grasp this concept!
 
Try this:
Copy this Expression into the query containing your table.
Obviously substitute the field names with your field
names.

CalcField: CalcAge([DateOfBirth]) & UCase([LName]) & UCase
([FName]) & DLookUp("ZipCode","tZip","ENTER YOUR CRITERIA
TO FILTER THE ZIP HERE")

Copy this function into a module (Taken from Code
Librarian):
Function CalcAge(dteBirthdate As Date) As Long

Dim lngAge As Long

' Make sure passed-in value is a date.
If Not IsDate(dteBirthdate) Then
dteBirthdate = Date
End If

' Make sure birthdate is not in the future.
' If it is, use today's date.
If dteBirthdate > Date Then
dteBirthdate = Date
End If

' Calculate the difference in years between today and
birthdate.
lngAge = DateDiff("yyyy", dteBirthdate, Date)
' If birthdate has not occurred this year, subtract 1
from age.
If DateSerial(Year(Date), Month(dteBirthdate), Day
(dteBirthdate)) > Date Then
lngAge = lngAge - 1
End If
CalcAge = lngAge
End Function

Hope this assists you.
 
I understand
using Queries to select certain info, but don't quite
grasp how to calculate the info. like my form did.
Click to expand...

Just type the same expression as you used in the Form Controls'
Control Source into a vacant Field cell in the query. E,g.

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd") >
Format(Date()), "mmdd"), 1, 0)
 
Back
Top