Access 2000 help

[iamwiz82]

First off, dont flame me cuz i'm using Access 2000, it's what they wanted!

Anyways, I'm creating a database of future business(including OEM, size, dates, etc.) for my company and one of the fields is Vehicle SOP(start of production) date. This date should be a date in mm/yy format. In access 2000, i set it up so the table is formatted to mm/yy and in the input mask, i set up 00\/00;0;; for the required inputs.

My problem is this:

when i go into the table and view the SOP dates, they are not correct. Each date is mm/02(no matter what, all 2002). When i click on each one, i get a more detailed date, which is mm/(the year i set it up for)/2002. So if i enter 07/04 for one, it now reads 7/4/2002(notice the lack of 2 digits in the month and day). How can i fix this?

 

[bunker]

What format do you want the data stored as?

mm/yy
mm/dd/yy
mm/dd/yyyy
m/d/yy
m/d/yyyy

Which one are you looking for as a final product? I'll play around with it and see what I can come up with.

 

[iamwiz82]

<< What format do you want the data stored as?

mm/yy
mm/dd/yy
mm/dd/yyyy
m/d/yy
m/d/yyyy

Which one are you looking for as a final product? I'll play around with it and see what I can come up with. >>



i want it to be mm/yy as a required field.

 

[bunker]

wiz, I can honestly say your little problem is beginning to piss me off! I just developed an entire surveillance system in Access 2000 and for some reason I can't figure this simple little problem out! 🙁

I really can't spend any more time on this as I'm quite busy. You may want to try this place out for help: www.tek-tips.com. There's a whole bunch of Access gurus over there.

 

[iamwiz82]

<< wiz, I can honestly say your little problem is beginning to piss me off! I just developed an entire surveillance system in Access 2000 and for some reason I can't figure this simple little problem out! 🙁

I really can't spend any more time on this as I'm quite busy. You may want to try this place out for help: www.tek-tips.com. There's a whole bunch of Access gurus over there. >>



thanks for the help bunker. I didnt think that this would be hard either, thats why i'm frustrated.

 

[bunker]

Okay, I just can't let it go 🙂. Are you going to be making any calculations off this date? If not you could just set it up as a number or text field instead of date/time.

He he, I'm trying to cheat 😉

 

[iamwiz82]

<< Okay, I just can't let it go 🙂. Are you going to be making any calculations off this date? If not you could just set it up as a number or text field instead of date/time.

He he, I'm trying to cheat 😉 >>



thats what i was going to resort to, but one of the queries is by SOP date and they want it sorted by date.

 

[bunker]

Buwhahahaha! Set the format to mm/yy. The line just above the input mask.

 

[iamwiz82]

<< Buwhahahaha! Set the format to mm/yy. The line just above the input mask. >>



it will sort by month, though, not by year and then month.

 

[bunker]

You can use a sql statement to do your sorts. If you put ORDER BY Date at the end of the sql it sorts by actual date, meaning year first.

Just an FYI - I never use access queries. I do all my queries using sql, so if you need help I can give you sql help, but not query help 🙂.

 

[bunker]

The only thing is, your input mask won't work with this. With your mask Access assumes you are entering Month/Day, not Month/Year. Tell your client they're gonna have to enter the entire date and change the mask. You can still have it display only month/year with the format though.

 

[EagleKeeper]

store the month/year as an text field.
create a second field that is month/01/year date field
Use the the first field for user interface entry. Use the second field for data storage and date sorting

 

[iamwiz82]

<< store the month/year as an text field.
create a second field that is month/01/year date field
Use the the first field for user interface entry. Use the second field for data storage and date sorting >>



how would i have VB do this?

Dim realmonth As String
Dim realyear As String

Left("vehicle sop", 2) = realmonth
Right("vehicle sop", 2) = realyear

that would pull my month and year, now how would i go about inserting the new string into a date box? What command inserts data into a table?

 

[EagleKeeper]

Drop this headache over in SW programming for specifics. Am not a VB expert. Could give you the VC equivalent though if you need it.

Look at a VBA sample in Access or read up on Database handling in a VB manual.
The Northwind sample in Access is a good start on reading and writing to a table.
Once you have ripped apart the month and year, then you will need to convert them into integers and build a date using an VB data type function.

You will have to create a database object and a table object using the database object.
Since you wil be adding a new record to the table, there will be a function that will be like AddNew.
You will then update any fields and then call a function like Update