Input mask not working with lookup

[shelibels]

Hi. I used an input mask (0000\-00;;_ to separate the year and pay period, i.e. 2008-20) in a field of one table that I'm using as a lookup field in a second table. I included an input mask on the same field in the second table, but when the list drops down in the second table, the input mask is not working. It shows 200820 with no dash. Any suggestions?

Thanks,
Shelibels

 

[dhookom]

Since you haven't gotten an answer yet, I assume others have the same opinion I have of both lookup fields

http://www.mvps.org/access/lookupfields.htm

and input masks. I don't use either mis-feature especially not in table designs.

Duane
Hook'D on Access
MS Access MVP

 

[Lilliabeth]

And check out the second commandment:

http://www.mvps.org/access/tencommandments.htm

Lilliabeth
-Why use a big word when a diminutive one will do?-

 

[shelibels]

I appreciate both of you responding. I now know not to use input masks. Does anyone have a suggestion on what TO do? The user is going to need to add more calendar years (4 digits) and pay periods (2 digits) as time goes. I was concerned that 200820 would be harder to read than 2008-20.
Thanks,
Shelibels

 

[dhookom]

I would at least consider using two fields [CalYear] and [PayPeriod]. You can then output them with a hyphen if needed.

I assume this field is text. You could use code in the after update event that would check for the hyphen and if it didn't exist, add it.

Duane
Hook'D on Access
MS Access MVP

 

[shelibels]

Duane,

Thanks for the response. I hope this makes sense...

I originally started there, but then got confused trying to create the lookup for the other table. I don't have much experience. I thought I needed an autonumber primary key since I need both the calendar year and pay period to create a unique field since we will have a 2008-01, 2009-01, etc., but now I don't know how to get the data into the table in a manner it makes sense. I don't want to pull the autonumber since it doesn't mean anything, but how do I get the year and the pay period into the other table?

I had created tbl_PayPeriods:
PayPeriodID, Autonumber, Primary Key
CalendarYear, Text (the calendar year)
PayPeriod, Text (a 2 digit number between 01 & 26)
PayPeriodBeginDate, Date (the date the pay period begins)
PayPeriodEndDate, Date (the date the pay period ends)

The second table, tbl_Overtime tracks type of overtime by pay periods:

OvertimeID, Autonumber, Primary Key
PayPeriodID, Number, Foreign Key (where I am trying to use the Calendar Year and Pay Period)
OT_SVrWx, Number, (how many hours of OT for severe weather)
OT_Sick, Number, (how may hours of OT for sickness)

Thanks,
Shelley

 

[dhookom]

You should be able to live with "autonumber since it doesn't mean anything". I use autonumbers all the time but rarely if ever expose them to the users. It shouldn't make any difference what the value of PayPeriodID is.

Also, I would normalize the tbl_Overtime. A better structure (IMHO) would be:
[tt][blue]
tbl_OverTime
=================
OvertimeID autonumber Primary Key
PayPeriodID foreign key to tbl_PayPeriods.PayPeriodID
OTTypeID foreign key to tbl_OTTypes.OTTypeID
OT_Hours Numeric for number of hours
[/blue][/tt]
This would require a lookup table for overtime types
[tt][blue]
tbl_OTTypes
=================
OTTypeID autonumber Primary Key
OTTitle text value like "Severe Weather","Sickness","..."
[/blue][/tt]


Duane
Hook'D on Access
MS Access MVP