Allen Browne's "Limiting a Report to a Date Range"

[Tony Girgenti]

I tried using Allen Browne's "Limiting a Report to a Date Range" example.
Listed below are all the dates in the post_dat field (defined as TEXT data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a debug.print of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat > #01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Ken Snell]

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"

 

[Tony Girgenti]

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range" example.
Listed below are all the dates in the post_dat field (defined as TEXT data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a debug.print of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat > #01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Ken Snell]

Sorry...I neglected to notice that you'd posted examples of the values of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range" example.
Listed below are all the dates in the post_dat field (defined as TEXT data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a

debug.print
of

"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat > #01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Tony Girgenti]

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the values of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range" example.
Listed below are all the dates in the post_dat field (defined as

TEXT
data

type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat >
#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Allen Browne]

Tony, Ken's suggestion of converting the text field to a date and comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real Date/Time
field in your table, populate it with an Update query, and then dump the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the values of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range"
example.
Listed below are all the dates in the post_dat field (defined as TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat >
#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Tony Girgenti]

Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could what you
suggest but it would have to happen every time this program runs. I can't
change the data in the original tables, that would mean changing all of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real Date/Time
field in your table, populate it with an Update query, and then dump the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the values of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are

tricky
for

doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range"
example.
Listed below are all the dates in the post_dat field (defined as TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a
debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat >
#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat >
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the way he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Allen Browne]

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

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

Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could what you
suggest but it would have to happen every time this program runs. I can't
change the data in the original tables, that would mean changing all of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real Date/Time
field in your table, populate it with an Update query, and then dump the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the

values
of

post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky
for
doing direct comparisons when using number- or date/time-like values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range"
example.
Listed below are all the dates in the post_dat field (defined as
TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a
debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat >
#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat
#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up the

way
he

expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Tony Girgenti]

Hi Allen.

Am i suppose to replace the "d" in your example with "sa_hdr.post_dat" ?

I did try that and it seemed to work OK in the query, but it does not give
me the results i want in the DoCmd.OpenReport command.

On thing i'm not sure about. According to the docs i read in Access and on
the internet, the "DateSerial" returns a date of "May 31, 1980". Is this
what we want ? I thought we were trying to get to a date of "05/31/1980".

Thanks,
Tony

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

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

Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could what you
suggest but it would have to happen every time this program runs. I can't
change the data in the original tables, that would mean changing all of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real Date/Time
field in your table, populate it with an Update query, and then dump the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the values
of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are tricky
for
doing direct comparisons when using number- or date/time-like
values.

You can use Allen's example by making a small change for your setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range"
example.
Listed below are all the dates in the post_dat field

(defined

as "post_dat

 

[Allen Browne]

Yes, use the name of your date field where I used the "d" in the example.

Internally, Access stores the date as a number. How it presents it to you
depends on your Regional Options in the Windows Control Panel. That means
that once you have a real date, the presentation is irrelevant.

In the WhereCondtion of the OpenReport action, you are now working with a
real date, so you must format the dates in the WhereCondition as:
#mm/dd/yyyy#

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

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

Hi Allen.

Am i suppose to replace the "d" in your example with "sa_hdr.post_dat" ?

I did try that and it seemed to work OK in the query, but it does not give
me the results i want in the DoCmd.OpenReport command.

On thing i'm not sure about. According to the docs i read in Access and on
the internet, the "DateSerial" returns a date of "May 31, 1980". Is this
what we want ? I thought we were trying to get to a date of "05/31/1980".

Thanks,
Tony

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

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

Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could

what
you

suggest but it would have to happen every time this program runs. I can't
change the data in the original tables, that would mean changing all of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and
comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real Date/Time
field in your table, populate it with an Update query, and then dump the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the values
of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465': Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are
tricky
for
doing direct comparisons when using number- or date/time-like
values.

You can use Allen's example by making a small change for your
setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date Range"
example.
Listed below are all the dates in the post_dat field

(defined

as
TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i get a
debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of

"post_dat

#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of "post_dat

#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything up

the
way

he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Tony Girgenti]

Hi Allen,

How do i put a "#" around this ?
I tried and get a syntax error.

"AND (DateSerial(Left([sa_hdr.post_dat], 4), Mid([sa_hdr.post_dat], 5,2),
Right([sa_hdr.post_dat],2)) >="

Thanks,
Tony

Yes, use the name of your date field where I used the "d" in the example.

Internally, Access stores the date as a number. How it presents it to you
depends on your Regional Options in the Windows Control Panel. That means
that once you have a real date, the presentation is irrelevant.

In the WhereCondtion of the OpenReport action, you are now working with a
real date, so you must format the dates in the WhereCondition as:
#mm/dd/yyyy#

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

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

Hi Allen.

Am i suppose to replace the "d" in your example with "sa_hdr.post_dat" ?

I did try that and it seemed to work OK in the query, but it does not give
me the results i want in the DoCmd.OpenReport command.

On thing i'm not sure about. According to the docs i read in Access and on
the internet, the "DateSerial" returns a date of "May 31, 1980". Is this
what we want ? I thought we were trying to get to a date of "05/31/1980".

Thanks,
Tony

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I

can't
see

any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could what
you
suggest but it would have to happen every time this program runs. I can't
change the data in the original tables, that would mean changing all of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and
comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems very
inefficient. Is there a good reason why you cannot create a real
Date/Time
field in your table, populate it with an Update query, and then

dump
the

text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the
values
of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465':
Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared to a
date/time-formatted field, not a text field. Text fields are
tricky
for
doing direct comparisons when using number- or date/time-like
values.

You can use Allen's example by making a small change for your
setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

I tried using Allen Browne's "Limiting a Report to a Date
Range"
example.
Listed below are all the dates in the post_dat field (defined
as
TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i

get

a

 

[Allen Browne]

You could wrap it in Format(), or you could build your string from the
existing one:

"#" & Mid([sa_hdr.post_dat], 5,2) & "/" &
Right([sa_hdr.post_dat],2) & "/" &
Left([sa_hdr.post_dat],4) & "#"


Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Hi Allen,

How do i put a "#" around this ?
I tried and get a syntax error.

"AND (DateSerial(Right([sa_hdr.post_dat],2)) Mid([sa_hdr.post_dat], 5,2),
Right([sa_hdr.post_dat],2)) >="

Thanks,
Tony

Yes, use the name of your date field where I used the "d" in the example.

Internally, Access stores the date as a number. How it presents it to you
depends on your Regional Options in the Windows Control Panel. That means
that once you have a real date, the presentation is irrelevant.

In the WhereCondtion of the OpenReport action, you are now working with a
real date, so you must format the dates in the WhereCondition as:
#mm/dd/yyyy#

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

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

Hi Allen.

Am i suppose to replace the "d" in your example with "sa_hdr.post_dat" ?

I did try that and it seemed to work OK in the query, but it does not give
me the results i want in the DoCmd.OpenReport command.

On thing i'm not sure about. According to the docs i read in Access

and
on

the internet, the "DateSerial" returns a date of "May 31, 1980". Is this
what we want ? I thought we were trying to get to a date of "05/31/1980".

Thanks,
Tony

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't
see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i

could
what

you
suggest but it would have to happen every time this program runs. I
can't
change the data in the original tables, that would mean changing

all
of

their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and
comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly

seems
very

inefficient. Is there a good reason why you cannot create a real
Date/Time
field in your table, populate it with an Update query, and then dump
the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples of the
values
of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) & "/" &
Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465':
Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

The use of the # delimiter is for values being compared

to

a

date/time-formatted field, not a text field. Text fields are
tricky
for
doing direct comparisons when using number- or date/time-like
values.

You can use Allen's example by making a small change for your
setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

message
I tried using Allen Browne's "Limiting a Report to a Date
Range"
example.
Listed below are all the dates in the post_dat field
(defined
as
TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3, i

get

a
debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of "post_dat

#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of
"post_dat

#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set everything

up
the

way
he
expalined.

Any help appreciated.

Thansk,
Tony

POST_DAT
19990926
19990926
19990926
19990929
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
19991003
20000112
20000112
20000112
20000206
20000206
20000206
20000309
20000309
20000309
20000407
20000407
20000407
20000407
20000508
20000508
20000508
20000612
20000612
20000612
20000714
20000714
20000714
20000812
20000812
20000812
20010101
20010101
20010101
20010101
20010101
20010101
20010101
20010112
20010112
20010112
20010112
20010112
20010123
20010123
20010123
20010123
20010123
20010128
20010128
20010128
20010128
20010128
20010128
20010131
20010131
20010131
20010131
20010131
20010131
20010203
20010203
20010203
20010203
20010203
20010203
20010203
20010205
20010205
20010205
20010205
20010205
20010209
20010209
20010209
20010209
20010209
20010218
20010218
20010218
20010218
20010218
20010228
20010228
20010228
20010228
20010228
20010301
20010301
20010301
20010301
20010301
20010302
20010302
20010302
20010302
20010302
20010303
20010303
20010303
20010303
20010303
20010305
20010305
20010305
20010305
20010305
20010309
20010309
20010309
20010309
20010309
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010329
20010402
20010405
20010407
20010408
20010408
20010413
20010414
20010414
20010414
20010416
20010419
20010421
20010423
20010503
20010503
20010503
20010505
20010505
20010507
20010510
20010512
20010512
20010512
20010512
20010514
20010517
20010519
20010519
20010525
20010527
20010527
20010601
20010602
20010602
20010604
20010604
20010607
20010607
20010609
20010609
20010611
20010611
20010614
20010614
20010616
20010616
20010618
20010618
20010621
20010621
20010623
20010623
20010625
20010701
20010701
20010702
20010702
20010706
20010706
20010708
20010708
20010709
20010709
20010709
20010712
20010714
20010714
20010714
20010714
20010716
20010716
20010716
20010716
20010719
20010719
20010721
20010721
20010723
20010723
20010726
20010726
20010728
20010728
20010730
20010730
20010802
20010802
20010804
20010804
20010806
20010806
20010809
20010809
20010811
20010811
20010813
20010813
20010816
20010816
20010818
20010818
20010820
20010820
20010823
20010823
20010825
20010825
20010827
20010827
20010830
20010830
20010831
20010901
20010901
20010903
20010903
20010907
20010907
20010909
20010909
20010910
20010913
20010913
20010913
20010915
20010915
20010917
20010917
20010917
20010920
20010920
20010922
20010922
20010924
20010924
20010927
20010927
20010929
20011001
20011001
20011004
20011004
20011004
20011004
20011006
20011006
20011006
20011008
20011008
20011008
20011011
20011011
20011011
20011013
20011013
20011015
20011015
20011018
20011018
20011018
20011020
20011020
20011020
20011022
20011022
20011025
20011025
20011027
20011027
20011029
20011029
20011101
20011101
20011101
20011103
20011103
20011104
20011104
20011104
20011105
20011105
20011105
20011105
20011108
20011108
20011108
20011109
20011110
20011110
20011111
20011111
20011111
20011112
20011112
20011115
20011115
20011116
20011116
20011116
20011116
20011117
20011117
20011117
20011119
20011119
20011122
20011122
20011123
20011123
20011124
20011126
20011126
20011126
20011129
20011129
20011130
20011130
20011201
20011201
20011201
20011201
20011202
20011202
20011203
20011203
20011203
20011206
20011206
20011207
20011207
20011208
20011208
20011209
20011209
20011210
20011210
20011213
20011213
20011214
20011214
20011215
20011215
20011216
20011216
20011217
20011217
20011220
20011220
20011221
20011221
20011221
20011222
20011222
20011223
20011223
20011223
20011224
20011224
20011227
20011227
20020103
20020103
20020105
20020105
20020106
20020106
20020107
20020107
20020107
20020110
20020110
20020112
20020112
20020112
20020112
20020114
20020114
20020114
20020117
20020117
20020119
20020121
20020121
20020121
20020124
20020124
20020126
20020126
20020128
20020128
20020131
20020201
20020201
20020201
20020203
20020203
20020207
20020207
20020209
20020209
20020209
20020211
20020211

 

[Tony Girgenti]

Hi Allen,

When i try that, i'm back to: "Run-time Error '2465':
Microsoft Access can't find the field '|' referred to in your expression.".

Thanks,
Tony

You could wrap it in Format(), or you could build your string from the
existing one:

"#" & Mid([sa_hdr.post_dat], 5,2) & "/" &
Right([sa_hdr.post_dat],2) & "/" &
Left([sa_hdr.post_dat],4) & "#"


Allen Browne - Microsoft MVP. Perth, Western Australia.

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

Hi Allen,

How do i put a "#" around this ?
I tried and get a syntax error.

"AND (DateSerial(Right([sa_hdr.post_dat],2)) Mid([sa_hdr.post_dat], 5,2),
Right([sa_hdr.post_dat],2)) >="

Thanks,
Tony

Yes, use the name of your date field where I used the "d" in the example.

Internally, Access stores the date as a number. How it presents it to you
depends on your Regional Options in the Windows Control Panel. That means
that once you have a real date, the presentation is irrelevant.

In the WhereCondtion of the OpenReport action, you are now working

with

a

"sa_hdr.post_dat"

?

I did try that and it seemed to work OK in the query, but it does

not
give

me the results i want in the DoCmd.OpenReport command.

On thing i'm not sure about. According to the docs i read in Access and
on
the internet, the "DateSerial" returns a date of "May 31, 1980". Is this
what we want ? I thought we were trying to get to a date of "05/31/1980".

Thanks,
Tony

Create a query into that data.
In the query, enter a calcuated field like this:
TrueDate: DateSerial(Left([d], 4), Mid([d], 5,2), Right([d],2))

If that works, you should be able to set your criteria on this field.

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

Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen. Thanks for your help.

As far as i can see, all of the dates in the data are valid. I can't
see
any with any bad fields or invalid dates.

The tables that i am using are from a Pervasive COBOL system and thru
externally linked ODBC tables, i can use the data. I guess i could
what
you
suggest but it would have to happen every time this program

runs.

I

can't
change the data in the original tables, that would mean changing all
of
their COBOL programs.

Thanks,
Tony

Tony, Ken's suggestion of converting the text field to a date and
comparing
it is workable provided:
- There is a value in every field: CDate() can't handle Nulls, and
- The text contains no invalid dates such as 20030229.

The whole idea of converting the text into dates constantly seems
very
inefficient. Is there a good reason why you cannot create a real
Date/Time
field in your table, populate it with an Update query, and

then
dump

the
text field?

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

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

Sorry Ken, I still get the same error.

Tony

Sorry...I neglected to notice that you'd posted examples

of
the

values
of
post_dat.

Try this (changes the text string into a mm/dd/yyyy string):

"CDate(Mid([post_dat],5,2) & "/" & Right([post_dat],2) &

"/"

&

Left([post_dat],4)) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

Hello Ken. Thanks for youe help.

When i try your suggestion, I get: "Run-time Error '2465':
Microsoft
Access
can't find the field '|' referred to in your expression.".

Thanks,
Tony

message
The use of the # delimiter is for values being

compared

to

a
date/time-formatted field, not a text field. Text

fields
are

tricky
for
doing direct comparisons when using number- or
date/time-like
values.

You can use Allen's example by making a small change for
your
setup:

"CDate([post_dat]) Between #01/01/2000# And #12/31/2003#"


--
Ken Snell
<MS ACCESS MVP>

message
I tried using Allen Browne's "Limiting a Report to a Date
Range"
example.
Listed below are all the dates in the post_dat field
(defined
as
TEXT
data
type in the table).

When i enter a range of dates like 1/1/0 to 12/31/3,

i
get

a
debug.print
of
"post_dat Between #01/01/2000# And #12/31/2003#"
and i get no records on the report.

If i enter 1/1/1 to blank, i get a debug.print of
"post_dat

#01/01/2001#"
and i get all the records in the table.

If i enter 12/31/3 to blank, i get a debug.print of
"post_dat

#12/31/2003#" and i get all the records in the table.

What am i doing wrong ? I made sure i set

everything

up