If there's a harder way to do something, I usually search tirelessly until I find it.
I have a people dbs with a form that includes ‘date of birth’ and ‘age’ calculated by a function. Additionally, I wanted to add the person’s astrological sign, looked up from source table, 'stbl_Sign':
So far the only way I have been able to successfully look up the ‘Sign’ is by converting ‘date of birth’ and...
David Blackman
Last Activity: Feb 5 '16, 06:22 AM
Joined: Feb 5 '11
Location: Home
-
Lookup table values using day-of-the-year integers
-
David Blackman replied to MS Access: Query rejects sorts when calculations are made field with null valuein AccessThanks for all the poop. I'm keeping your replies in OneNote for further reference.
What I'm doing is sorting ages into ranges; so if fn_Age returns a value that identifies the record as containing a null DOB, i.e. 999 as you suggest, I can simply use that value to exclude null records in the SQL WHERE clause.
You solved my problem. Thanks again. You folks are great. :) -
David Blackman started a topic MS Access: Query rejects sorts when calculations are made field with null valuein AccessMS Access: Query rejects sorts when calculations are made field with null value
I have a query that shows date of birth from a field and calculates age as a variant with a simple function:
[Age]: fn_Age([DOB])
If any record in the table contains a null [DOB], the query rejects all sorts based on the [Age] field.
I’ve tried using the ‘Is Not Null’ criteria on [DOB], but still get the “Data type mismatch in criteria expression” error.
Any suggestion? -
Thanks for responding. I finally go it worked out by using the following code in the OnCurrent event:
...Code:Dim Ctl(3) As ListBox Dim n As Integer Set Ctl(0) = List_Committee Set Ctl(1) = List_CommitteeMember Set Ctl(2) = List_SubCommittee Set Ctl(3) = List_SubCommitteeMember Ctl(0) = ComID Ctl(2) = ComSubID With Ctl(3)Leave a comment:
-
The statement
"regNo = format(CLng(pas s),"0000000")"
will change the string value to a long integer value, with the leading 0's.
But in that "regNo" is already showing the correct number, simply setting the field's "Format" property to "0000000" will restore the leading 0's.Leave a comment:
-
How to get listboxes to display values based on row data?
I have a form with 4 unbound listboxes (LB).
When the form opens, I want:
1. LB1 to display values in row 1;
2. LB3 to display data based on the value of LB1;
3. LB4 to display data based on the value of LB3;
4. LB2 to select the the row that agrees with row 1 of LB4
I can get this all to happen AFTER the form is opened, but not at the time of the OnOpen or...
No activity results to display
Leave a comment: