|
PREV &
NEXT
Car Stereos and MS Access Tricks I have a page which Google and Alta Vista keep sending people to. The subject is how to set the clock on a certain radio for your car. I recently searched for how to unlock the Infinity CD player in the Mitsubishi Mirage that I had rented while my car is in the shop. I couldn�t find anything, but I�ll put that information here too. The last bit of info that I�m putting is how to calculate the age of something given the date in a non-date number structure like YYMMDD in only one line in Microsoft Access. Topic 1: How to set the Clock on a Pioneer DEH10 On Thanksgiving, my brother in law Brad and I were trying to set his car stereo's clock. Since he was out of town (and besides they had just moved) he wasn't able to get the manual. We tried everything, holding buttons for a few seconds, turning the power on and holding stuff, every logical thing that I could think. I called the 1-800 number and also checked the website but to no avail. Even the page that told us how to on the www.pioneerservice.com website was wrong. But then I got an email from Brad the other day: Date: Mon, 29 Nov 1999 18:04:00 -0600 (CST) Subject: Settin' the clock I finally found out how to set that danged clock on the car radio. It took a call to the Pioneer help line and an approx. 45-minute wait before the secret was passed on to me. Just in case you ever run across someone else with the same problem, here's how it's done: Turn ignition on. Press and hold source button to turn unit off. Press function button and hold for two seconds. Unit will light up and clock will flash in that classic after power outage "please set me" mode. Arrow buttons change time and toggle between hours/minutes. I told Brad that I'd pass this information on and submit this page to AltaVista. If this helped you, send me an email so I'll know. Topic 2: How to unlock the Infinity Stereo in a 2001 Mitsubishi Mirage If you have the power disconnected, or if the car is new, as was my situation, the car stereo in the Mirage I had requires you to enter the four digit code in to let you get into the stereo. If you enter the code incorrectly three times, it locks you out so that when you turn the power to the stereo on, rather than playing, or saying code so you can enter the code, it says off. The way to reset the stereo is to keep the power applied to it for a full hour. This was told the the guy at the rental place as leaving the car in the ACC position for an hour and then when you hit the power button it will ask for the code again. The code should have come on two little cards that were in the glove box. Or they were in the Mirage I had first. I�m not sure the one I have now has those cards in it. Topic 3: How to calculate the age of something in Access given a number in YYMMDD format The big problem here is that you are mixing numbers and dates. The date of birth, as I called it, or DOB#, can reveal the year by dividing the DOB# by 10000 and rounding. Rounding is installed in all Access builds where the TRUNC function is not. However, since there are only 12 months, it will always round down. By taking the YEAR and NOW functions you can get the year you are currently in. This will be a four digit number. Now it�s 2001. All the DOB#s I worked with were in the 1900s. By taking the year now and subtracting 1900 and the DOB year, you get the age that you will turn this calendar year. However, my birthday, October 18, has not come yet. Next you need to figure out how to generate a one or zero without using an IF-THEN statement. I did this by getting the last four digits of the DOB# and the four digits which represent the month and day of now. I got rid of the year portion of the DOB# by subtracting the DOB#/10000 rounded and then multiplied by 10000 from the DOB#. I generated the now digits by adding the result of the DAY function of the NOW function and the MONTH function of the NOW function times 100. I subtracted one from the other, divided the result by 10000, subtracted 0.5 (since I don�t have TRUNC on this Access), and rounded. This would give me a minus one if the birthday had not happened and a zero if it had. Once this number is generated, you can add it to the age you will turn this year. Or, if you can�t follow that, you sure can�t follow this: ageyear: [ageyearw]+[gimmiea1] ageyearw: Year(Now())-1900-round(([dob#]/10000),0) gimmiea1: ROUND([gimmiea1p4]-0.5,0) gimmiea1p4: [gimmiea1p3]/10000 gimmiea1p3: [gimmiea1p2]-[gimmiea1p1] gimmiea1p2: Day(Now())+(Month(Now())*100) gimmiea1p1: [dob#]-(round(([dob#]/10000),0)*10000) So... gimmiea1: ROUND((((Day(Now())+(Month(Now())*100))-([dob#]-(round(([dob#]/10000),0)*10000)))/10000)-0.5,0) And... ageyear: (Year(Now())-1900-round(([dob#]/10000),0))+(ROUND((((Day(Now())+(Month(Now())*100))-([dob#]-(round(([dob#]/10000),0)*10000)))/10000)-0.5,0)) That should be in a format you can just copy and paste into Access. Now, don�t ever say I didn�t do anything nice for you. Send me an email if you find this useful. I got lots of emails for the DEH stereo. |