AAARGH!!! Help needed with misbehaving form link criteria

[ruralguy via AccessMonster.com]

Great David. When you are ready you can send it to Rural Guy at Wild Blue dot
Net. I'll post back to this thread if I find out anything plus send you a
ping email as well.

Allan,
I'll be happy to provide a stripped down Access 2000 MDB file that
demonstrates my date linking problem. Thanks for the kind offer. Thanks also
for expanding my vocabulary with the word 'octothorp'!! That's not a word we
use on this side of the pond (it doesn't even get a mention in my
heavyweight two-volume edition of the Oxford English Dictionary). I've only
ever seen it referred to as the hash symbol before, but octothorp certainly
sounds a bit classier.

Albert had both octothorps and forward slashes in his code sample but he
didn't have backslashes. To quote your Allan Browne link, "Unfortunately,
Format() replaces the slashes with the date separator character defined in
Control Panel | Regional Settings, so you must specify literal slashes in
the format string by preceding the slash with backslashes". For example, his
code sample included the following: Format(Me.StartDate, "mm\/dd\/yyyy").

I tried that method, but it didn't work for me. In any case, my UK regional
settings use the same forward slash date separator as US dates so it
shouldn't make any difference in my case, as indeed I found.

David

The back slashes are only necessary if you are using the Format() function
to

[quoted text clipped - 8 lines]

 

[Stuart McCall]

Thanks also for expanding my vocabulary with the word 'octothorp'!! That's

not a word we use on this side of the pond (it doesn't even get a mention
in my heavyweight two-volume edition of the Oxford English Dictionary).
I've only ever seen it referred to as the hash symbol before, but octothorp
certainly sounds a bit classier.

I agree. I came across the word a year or two ago, and the following was put
forward as its origin:

The term "octothorp(e)" was invented for "#", allegedly by Bell Labs
engineers when touch-tone telephones were introduced in the mid-1960s.
"Octo-" means eight, and "thorp" was an Old English word for _village_:
apparently the sign was playfully construed as eight fields surrounding a
village.

(I still call it the hash symbol though)

 

[David Anderson]

A very small self-explanatory A2k database has now been sent to your email
address. I will be very interested to see if you can shed any light on this
mysterious behaviour. I will not be surprised if it turns out to be nothing
more than a simple error on my part....

Thanks for the assistance.

David

 

[ruralguy via AccessMonster.com]

It has arrived. I'll let you know if I come up with something.

 

[David Anderson]

PROBLEM NOW SOLVED (!) - with thanks to both Albert Kallal and Allan Bunch.

The key was in Albert's post earlier today when he said "date match only
queries will fail, you have to include the a time part" [if the dates stored
in the table include a time component]. My stored PaymentDate values were
indeed originally created using Now() and extending my Format function to
Format(Me![PaymentDate], "mm\/dd\/yyyy hh:mm:ss") was the final solution to
my linking problem.

I hadn't yet read Albert's last post properly, as I was busy creating a
simple problem demo database for Allan, so it was Allan who actually brought
my attention to this specific omission in my code. Thanks a bundle to both
of you. The assistance is much appreciated.

I have learned several things during the course of this rather extended
discussion; the need for US formatted dates in SQL, the need for backslashes
in some instances of the Format function and, finally, the fact that
matching date/time is a little trickier than it looks.

I was about to write that these Access forums are worth their weight in
gold, but I'm not sure that online forums weigh anything at all so that
could be taken as an unintended insult! Let me simply say that I would be
lost without the the online help of dedicated people such as Albert and
Allan.

David

I'm tearing my hair out over a bug that has just arisen in one of my
Access 2000 forms - at least I would be, if I had any hair left.

I have a bound continuous form of payment records (called PaymentsList)
with a command button that opens a modal popup form (called EditPayments)
for editing a specific payment. The standard Access 2000 command button
wizard click event code is as follows,

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "EditPayments"

stLinkCriteria = "[PaymentDate]=" & "#" & Me![PaymentDate] & "#"
DoCmd.openForm stDocName, , , stLinkCriteria


Up to three days ago, this worked fine. I could create a new payment and
edit it immediately. Yesterday, everything went pear-shaped. Any old
record in my Payments table (saved 3 or more days ago) can be edited but
no newly created record is found via the [PaymentDate] linking criterion.
The popup recordset has a RecordCount of zero why I try to edit a recent
record. This behaviour is consistent, i.e. all old payment records can be
edited but none of the new ones. I can't remember making any recent design
changes of potential relevance to this problem (but my memory is
fallible).

The underlying query of my PaymentsList form is quite happy to find a
newly created payment record using a specified payment date, so why is my
DoCmd.openForm process failing? How can it find one record but not
another, when they both have the same type of information stored in the
Payments table?

Suspecting some form of database corruption, I have created new versions
of both my front and back end, importing all the tables, etc, from the
original versions. I have tried deleting all the indexes from my Payments
table definition, saving it, and then recreating the indexes. So far,
nothing has worked.

I'm totally flummoxed. Any guidance on this would be greatly appreciated!

David