Open form linked to system date

[ajk]

I want to open a specific form (a form with appointments for staff members)
and display only the entries for a specific date i.e the system date.

How would I be able to do that?

I have two tables, one for Attendee info and one for venue info.

The Venue table is used as the main source for the "main" form with the
attendee table being a subform.

Further, on a date where there are no meetings can the following meeting
date be shown?


Thanks again

 

[Crystal]

Hi ajk,

use the OnLoad event of the form

me.Filter = "DateValue(DateFieldname)=#" & date() & "#"
me.FilterOn = true

If your subform uses the date field for LinkMasterFields and
LinkChildFields, you should only have to do this in the code
behind the main form

to see how many records were returned -->
Me.Recordset.RecordCount

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Douglas J Steele]

Don't forget, Crystal, that some users might have their short date format
set to dd/mm/yyyy, in which case what you've suggested will not work for the
first 12 days of each month.

Safer is

me.Filter = "DateValue(DateFieldname)=" & Format(Date(), "\#mm\/dd\/yyyy\#")

or

me.Filter = "DateValue(DateFieldname)=" & CLng(Date())

 

[Crystal]

Thanks, Douglas!

I have had troubles using date functions with foreign
databases... now I know why!

Thank you so much for tagging on...

I have never thought to use # in the Format code -- makes sense!

So, CLng(Date()) returns the serial number... does Date()
just return the American format?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[ajk]

Thank you both!

Very much appreciated

Don't forget, Crystal, that some users might have their short date format
set to dd/mm/yyyy, in which case what you've suggested will not work for the
first 12 days of each month.

Safer is

me.Filter = "DateValue(DateFieldname)=" & Format(Date(), "\#mm\/dd\/yyyy\#")

or

me.Filter = "DateValue(DateFieldname)=" & CLng(Date())

 

[Douglas J Steele]

Date values in VBA don't have a format: they're strictly an 8 byte floating
point number (the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day)

However, when you concatenate the Date function into a string, Access will
coerce the date into a string, using whatever Short Date format has been set
on the machine. If that format happens to be dd/mm/yyyy, you'll run into
problems for the first 12 days of each month (Access is smart enough to
treat a date like 21/02/2006 correctly, but it's going to say that
10/02/2006 is October 2nd)

For more information about working with International Dates, see Allen
Browne's "International Dates in Access" at
http://allenbrowne.com/ser-36.html or what I had in my September 2003 Access
Answers column for Pinnacle Publication's "Smart Access" newsletter. (The
column and accompanying database can be downloaded for free at
http://www.accessmvp.com/djsteele/SmartAccess.html)

 

[RoyVidar]

Thanks, Douglas!

I have had troubles using date functions with foreign databases... now I know
why!

Thank you so much for tagging on...

I have never thought to use # in the Format code -- makes sense!

So, CLng(Date()) returns the serial number... does Date() just return the
American format?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

Also ISO 8601 is widely used
http://en.wikipedia.org/wiki/ISO_8601

"yyyy-mm-dd", "yyyy-mm-dd hh:nn:ss"

Allen Brownes explaines some of the date challenges here
http://allenbrowne.com/ser-36.html

Clng(date) - be aware though that playing with those numbers might
provide some anomalities on other platforms. 38769 might be todays date
for Jet, and will probably work quite well within the Jet interface,
but
you might experience some anomalities with other platforms, depending
on
in which way you pass this serialnumber.

Try SQL server - execute a query passing todays serialnumber both on a
linked table and on a separate (ADO) connection to the same table and
check the results (my guess is they would be they are two days off)

I don't know much about the inner workings of the Date function, but I
suppose it returns a value representing today date as the datatype Date
(which I think is an 8 byte floating point datatype (similar to, or the
same as Double?), where the decimal part is zero). I don't think the
Date function itself returns a specific format, but that the format is
determined and applied whenever a value of this datatype is presented
in
the UI, IDE or concatenated into strings. If a format is specified for
the form/report control, string or whatever, this is used, else Access
fetches the date format from the regional settings.

When using dynamic SQL, we're passing a string to the db engine, where
we need to make sure the information we send is as unamiguous as
possible, so that it is interpreted correct regardless of locale. For
dynamic SQL strings, such formatting might be necessary for both dates
and non-integer numeric datatypes (which might make it interesting to
start utilizing the parameters collection of the objects one ar using
in
stead ;-) )

 

[G and (ajk)]

We would like to test the form by using a future date. The system date was
changed to a date in the future with data but the form only produced a blank
form.
On entering a date in Feb - (not today) the records are displayed but
anything in March or April are not. Even a date in January are displayed
correctly.

Can you assist?

Thanks

 

[RoyVidar]

G and (ajk) wrote in message

We would like to test the form by using a future date. The system date was
changed to a date in the future with data but the form only produced a blank
form.
On entering a date in Feb - (not today) the records are displayed but
anything in March or April are not. Even a date in January are displayed
correctly.

Can you assist?

Thanks

Perhaps if we could see your current code?

Also - ensure you do have records in the table you are showing for
future dates.

 

[Crystal]

Thank you very much, Douglas! (or do you prefer Doug?)

I have noted your link so I can read your articles in more
detail later. I did read the one on dates and, aside from
understanding dates better, I learned a new word...
octothorpes (#) ! Yay! So is the singular form "octothorpe"?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Crystal]

Hi Roy,

thank you for elaborating...

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[G and (ajk)]

The code we are currently using to show record in the form for today is
(please note we are using the UK date format):

Me.Filter = "DateValue(StartDate)=" & Format(Date, "\#dd\/mm\/yyyy\#")
Me.FilterOn = True

However, when you change the system date to a date after February it
produces no records, even though there are records for that given date. The
past records however, say if you change the system date to a date in January
(where there are records), still appear.

Any help would be greatly appreciated.

G and ajk

 

[Douglas J Steele]

You've missed the point of what Crystal & I have been talking about.

Regardless of what date format you're using, you MUST use

Me.Filter = "DateValue(StartDate)=" & Format(Date, "\#mm\/dd\/yyyy\#")

(okay, that's not strictly true: you could use an unambiguous format, such
as yyyy-mm-dd or dd mmm yyyy. The point is, you cannot use dd/mm/yyyy)

 

[Douglas J Steele]

Thank you very much, Douglas! (or do you prefer Doug?)

I have noted your link so I can read your articles in more
detail later. I did read the one on dates and, aside from
understanding dates better, I learned a new word...
octothorpes (#) ! Yay! So is the singular form "octothorpe"?

I deliberately searched for that word, as I was tired of seeing people refer
to it as "delimit with number signs" or "delimit with pound signs",
especially since that latter expression seems to confuse people, as to many
the "pound sign" is £. (Hopefully that came through as the symbol for Pounds
Sterling!)

I'm a classically-trained musician. They're sharps, dammit! <g>


And it's Doug.

 

[Bob Miller]

In the query upon which the form is based, put =Date() in the Criteri
for the date field.

 

[Crystal]

thanks, Doug

I like the "sharp" idea as I have a piano that I love
playing, I just love music period.

What instruments do you like the most?

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com