Same filter, same datatype but only some records filtered

B

Barry Claxton

This has been driving me bats for a full week! How can a filter work on SOME
records in a table (in this case dates) but not others in the same table? By
the way I created the database in English for a fishing club of which I'm
secretary. The INTERFACE (and table names) are for a Spanish user but its
programing is English:

I have two forms serving two tables. The table "Registros de Pesca" stores
details/history of each members days of fishing. The table "Datos de
capturas" stores details of fish caught by each fisher. the two forms are
respectively "registros de pesca historia" & "Datos de capturas form"

On the form "registros de pesca historia" each record has a button
"capturas" which when clicked opens the form "Datos de capturas form"
I'm trying to have the "Datos de capturas form" function like a sub form.
i.e it opens with two fields on each form synchronised.
The fields in question (on forms and tables) are;- [Nº REGISTRO] and FECHA
DE EMISIÓN DEL TICKET (On the form "registros de pesca historia") and
Nº_REGISTRO] and TICKET_FECHA_DE_EMISIÓN (on the the form "Datos de capturas
form")

Behind the capturas button the code is as follows:-

Private Sub Capturas_Click()
On Error GoTo Err_Capturas_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Datos de capturas form"
stLinkCriteria = "[Nº_REGISTRO]=" & Me![Nº REGISTRO]
stLinkCriteria = "[TICKET_FECHA_DE_EMISIÓN]=" & "#" & Me![FECHA DE
EMISIÓN DEL TICKET] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Capturas_Click:
Exit Sub
Err_Capturas_Click:
MsgBox Err.Description
Resume Exit_Capturas_Click
End Sub

Now, two things, the program only appears to be seeing the reference to the
Fecha de emision fields, BUT here's what is really perplexing me,

When I display the Registros de pesca form for (say) member 1115, his 12
different days of fishing are displayed. Now, if i click on th capturas
button on each record in turn, SOME of the capturas details in the capturas
form display, synchronised accordingly. However, SOME do not! The amazing
thing is, the filtering (created by the above procedure) is the same, or
that's to say correct, for each record

e.g. in the "Data" properties opposite "filter" for the capturas form
appears [TICKET_FECHA_DE_EMISIÓN]=#26/01/2009#
correct for that form and displaying correctly. However, another record
(with a different date) from this member 1115 after clicking capturas
displays the two fields on it blank (no data), DESPITE the filter being there
and being correct! (e.g. [TICKET_FECHA_DE_EMISIÓN]=#03/06/2008#)

How can it be that the same code sets up the same filter and yet some
records show and others dont !!! The underlying data is the same for each
record in the tables!

Anyone help me with this?
 
A

Allen Browne

A literal date in a SQL string needs to be formatted the American way, not
according to your own local date format:

stLinkCriteria = "[TICKET_FECHA_DE_EMISIÓN]=" & _
Format(Me![FECHA DE EMISIÓN DEL TICKET], "\#mm\/dd\/yyyy\#")

Details in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Barry Claxton said:
This has been driving me bats for a full week! How can a filter work on
SOME
records in a table (in this case dates) but not others in the same table?
By
the way I created the database in English for a fishing club of which I'm
secretary. The INTERFACE (and table names) are for a Spanish user but its
programing is English:

I have two forms serving two tables. The table "Registros de Pesca"
stores
details/history of each members days of fishing. The table "Datos de
capturas" stores details of fish caught by each fisher. the two forms are
respectively "registros de pesca historia" & "Datos de capturas form"

On the form "registros de pesca historia" each record has a button
"capturas" which when clicked opens the form "Datos de capturas form"
I'm trying to have the "Datos de capturas form" function like a sub form.
i.e it opens with two fields on each form synchronised.
The fields in question (on forms and tables) are;- [Nº REGISTRO] and
FECHA
DE EMISIÓN DEL TICKET (On the form "registros de pesca historia") and
Nº_REGISTRO] and TICKET_FECHA_DE_EMISIÓN (on the the form "Datos de
capturas
form")

Behind the capturas button the code is as follows:-

Private Sub Capturas_Click()
On Error GoTo Err_Capturas_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Datos de capturas form"
stLinkCriteria = "[Nº_REGISTRO]=" & Me![Nº REGISTRO]
stLinkCriteria = "[TICKET_FECHA_DE_EMISIÓN]=" & "#" & Me![FECHA DE
EMISIÓN DEL TICKET] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Capturas_Click:
Exit Sub
Err_Capturas_Click:
MsgBox Err.Description
Resume Exit_Capturas_Click
End Sub

Now, two things, the program only appears to be seeing the reference to
the
Fecha de emision fields, BUT here's what is really perplexing me,

When I display the Registros de pesca form for (say) member 1115, his 12
different days of fishing are displayed. Now, if i click on th capturas
button on each record in turn, SOME of the capturas details in the
capturas
form display, synchronised accordingly. However, SOME do not! The amazing
thing is, the filtering (created by the above procedure) is the same, or
that's to say correct, for each record

e.g. in the "Data" properties opposite "filter" for the capturas form
appears [TICKET_FECHA_DE_EMISIÓN]=#26/01/2009#
correct for that form and displaying correctly. However, another record
(with a different date) from this member 1115 after clicking capturas
displays the two fields on it blank (no data), DESPITE the filter being
there
and being correct! (e.g. [TICKET_FECHA_DE_EMISIÓN]=#03/06/2008#)

How can it be that the same code sets up the same filter and yet some
records show and others dont !!! The underlying data is the same for each
record in the tables!

Anyone help me with this?
Click to expand...
 
B

Barry Claxton

Alan, thanks a lot for such a prompt reply. I'll need a couple of days to
"play around" with your suggestion (I'm new to VBA) but your suggestion seems
logical. Watch this space!
 
B

Barry Claxton

Allen, THANKS A MILLION! after hours spent racking my brains you give me a
solution which took half an hour to implement! How can something so
fundementally wrong with an otherwise brilliant piece of software (Access) go
un- published by Microsoft? Thank goodness for the commitment of
"independents like yourself!

Barry Claxton said:
This has been driving me bats for a full week! How can a filter work on SOME
records in a table (in this case dates) but not others in the same table? By
the way I created the database in English for a fishing club of which I'm
secretary. The INTERFACE (and table names) are for a Spanish user but its
programing is English:

I have two forms serving two tables. The table "Registros de Pesca" stores
details/history of each members days of fishing. The table "Datos de
capturas" stores details of fish caught by each fisher. the two forms are
respectively "registros de pesca historia" & "Datos de capturas form"

On the form "registros de pesca historia" each record has a button
"capturas" which when clicked opens the form "Datos de capturas form"
I'm trying to have the "Datos de capturas form" function like a sub form.
i.e it opens with two fields on each form synchronised.
The fields in question (on forms and tables) are;- [Nº REGISTRO] and FECHA
DE EMISIÓN DEL TICKET (On the form "registros de pesca historia") and
Nº_REGISTRO] and TICKET_FECHA_DE_EMISIÓN (on the the form "Datos de capturas
form")

Behind the capturas button the code is as follows:-

Private Sub Capturas_Click()
On Error GoTo Err_Capturas_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Datos de capturas form"
stLinkCriteria = "[Nº_REGISTRO]=" & Me![Nº REGISTRO]
stLinkCriteria = "[TICKET_FECHA_DE_EMISIÓN]=" & "#" & Me![FECHA DE
EMISIÓN DEL TICKET] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Capturas_Click:
Exit Sub
Err_Capturas_Click:
MsgBox Err.Description
Resume Exit_Capturas_Click
End Sub

Now, two things, the program only appears to be seeing the reference to the
Fecha de emision fields, BUT here's what is really perplexing me,

When I display the Registros de pesca form for (say) member 1115, his 12
different days of fishing are displayed. Now, if i click on th capturas
button on each record in turn, SOME of the capturas details in the capturas
form display, synchronised accordingly. However, SOME do not! The amazing
thing is, the filtering (created by the above procedure) is the same, or
that's to say correct, for each record

e.g. in the "Data" properties opposite "filter" for the capturas form
appears [TICKET_FECHA_DE_EMISIÓN]=#26/01/2009#
correct for that form and displaying correctly. However, another record
(with a different date) from this member 1115 after clicking capturas
displays the two fields on it blank (no data), DESPITE the filter being there
and being correct! (e.g. [TICKET_FECHA_DE_EMISIÓN]=#03/06/2008#)

How can it be that the same code sets up the same filter and yet some
records show and others dont !!! The underlying data is the same for each
record in the tables!

Anyone help me with this?
Click to expand...
 
B

Barry Claxton

Allen, thanks A MILLION! Your solution solved my date filter problem. How can
something so fundamentally flawed in an otherwise brilliant piece of software
(Access) go completely anannounced to us users? Thank goodness for the time
and committment on the part of "independents" like yourselves, thanks once
again!


Barry Claxton said:
This has been driving me bats for a full week! How can a filter work on SOME
records in a table (in this case dates) but not others in the same table? By
the way I created the database in English for a fishing club of which I'm
secretary. The INTERFACE (and table names) are for a Spanish user but its
programing is English:

I have two forms serving two tables. The table "Registros de Pesca" stores
details/history of each members days of fishing. The table "Datos de
capturas" stores details of fish caught by each fisher. the two forms are
respectively "registros de pesca historia" & "Datos de capturas form"

On the form "registros de pesca historia" each record has a button
"capturas" which when clicked opens the form "Datos de capturas form"
I'm trying to have the "Datos de capturas form" function like a sub form.
i.e it opens with two fields on each form synchronised.
The fields in question (on forms and tables) are;- [Nº REGISTRO] and FECHA
DE EMISIÓN DEL TICKET (On the form "registros de pesca historia") and
Nº_REGISTRO] and TICKET_FECHA_DE_EMISIÓN (on the the form "Datos de capturas
form")

Behind the capturas button the code is as follows:-

Private Sub Capturas_Click()
On Error GoTo Err_Capturas_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Datos de capturas form"
stLinkCriteria = "[Nº_REGISTRO]=" & Me![Nº REGISTRO]
stLinkCriteria = "[TICKET_FECHA_DE_EMISIÓN]=" & "#" & Me![FECHA DE
EMISIÓN DEL TICKET] & "#"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Capturas_Click:
Exit Sub
Err_Capturas_Click:
MsgBox Err.Description
Resume Exit_Capturas_Click
End Sub

Now, two things, the program only appears to be seeing the reference to the
Fecha de emision fields, BUT here's what is really perplexing me,

When I display the Registros de pesca form for (say) member 1115, his 12
different days of fishing are displayed. Now, if i click on th capturas
button on each record in turn, SOME of the capturas details in the capturas
form display, synchronised accordingly. However, SOME do not! The amazing
thing is, the filtering (created by the above procedure) is the same, or
that's to say correct, for each record

e.g. in the "Data" properties opposite "filter" for the capturas form
appears [TICKET_FECHA_DE_EMISIÓN]=#26/01/2009#
correct for that form and displaying correctly. However, another record
(with a different date) from this member 1115 after clicking capturas
displays the two fields on it blank (no data), DESPITE the filter being there
and being correct! (e.g. [TICKET_FECHA_DE_EMISIÓN]=#03/06/2008#)

How can it be that the same code sets up the same filter and yet some
records show and others dont !!! The underlying data is the same for each
record in the tables!

Anyone help me with this?
Click to expand...
 
Top