Date/Time Help

[theRepublic]

Hey there,

This may be difficult to articulate but what I am trying to do is manipulate a Date/Time field to only display the days 1 or 16, for DD/MM/YYYY.

I think in order to do this it would be in the validation rule but I have no IDEA what the expression would be.

Any Ideas? Please Help.

 

[theRepublic]

I think it may have to do with the DateAdd expression.

DateAdd(interval, number, date)

I only want the days 1 or 16 for days. The months and year could be anything.

 

[RuralGuy]

Or maybe DateSerial(). What are the current values in the field? Are you trying to change the value or just how it displays?

 

[theRepublic]

I'm a really big Noob with ACCESS, But heres my situation. I making an orders table for my dads newspaper company. The newspaper issues are printed for the 1rst and the 16th of the month. So...When a Client wants to put an order in to advertise, I am trying to manipulate the date/time field to only be able to input 1 or 16 for DD.

There are no current values because nothing has been inputed yet. I am still creating the database.

 

[RuralGuy]

You may find this link helpful.

 

[MSAccessRookie]

DateSerial should be right for your needs. Go to the link provided by RuralGuy to get the command syntax.

 

[raskew]

Something like this (today is 30 Jun 08)

x = dateserial(year(date), month(date) + iif(day(date)>16,1,0), iif(day(date)>16, 1, 16))
? x
[U]7/1/2008[/U]

HTH - Bob

Added: (today is now 1 Jul 08)

x = dateserial(year(date), month(date) + iif(day(date)>16,1,0), iif(day(date)>16, 1, 16))
? x
[U]7/16/2008[/U]

 

[theRepublic]

Something like this (today is 30 Jun 08)

x = dateserial(year(date), month(date) + iif(day(date)>16,1,0), iif(day(date)>16, 1, 16))
? x
[U]7/1/2008[/U]

HTH - Bob

Added: (today is now 1 Jul 08)

x = dateserial(year(date), month(date) + iif(day(date)>16,1,0), iif(day(date)>16, 1, 16))
? x
[U]7/16/2008[/U]

So how do I incorporate this code into the field properties? do I copy and put this into the expression builder?

And will this code allow only advertusing orders to be placed for the 1rst and 16th?

 

[RuralGuy]

It looks like Bob is taking a well deserved break for a bit. If I were doing it I would use Bob's code in the BeforeUpdate event of the form that creates advertusing orders. Replacing x with the field name of your DateField.

 

[raskew]

Hi Allan -

Thanks for jumping in. Agree with your recommendation.

Would you mind refreshing your link (post #5). I'm getting an error that it can't find the file, then returns a partial URL.

Best wishes - Bob

 

[RuralGuy]

Hi Bob,
Hmmm....it works for me. It is a page on Graham Seach's site.
http://www.pacificdb.com.au/MVP/Code/Dates.htm

 

[raskew]

Thanks -

Very strange. It's not an Access problem. Copied the link straight to the browser and got the same message.

If I cut it down to http://www.pacificdb.com.au/ it processes but I can't find anything on the site that leads me to the /MVP/Codes/Dates.htm.. Oh well, one of life's little mysteries.

Bob

 

[RuralGuy]

Weird...Oh well. Have you tried clearing your browser cache?

 

[raskew]

Turns out I had, sometime in the past, bookmarked that site. Click on the bookmark and get the same response. Oh well?!

I assume you're able to go right to it with the link? I'm going to try it with IE (I'm still using Netscape).

Bob

Added: Went right to it with IE. That is bizarre.