A subform on an Access2000 database

[tunsarod]

A subform on an Access2000 database gets its data from a Select query whose datasource is a table in an AS400 database.

Without any criteria in the query all the records display in the subform.

The Access main form also has a textbox that carried a date value passed to it from a calendar control also on the main form. Now if the query is modified to include as a criteria a reference to the textbox: [Forms]![myForm]![myTextbox] no records are displayed even when the textbox date matches the date field in the data table. The date field in the table which is being compared to the textbox on the form is a TEXT type not a DATE type.

However when this process is tested against an apparently identical table within the Access database it works fine whether or not the Date field is date or text type.

Can anyone tell me whether the Access/AS400 connection requires that the query criteria's reference to the textbox object should be written differently?

Rod

 

[tunsarod]

I see I got no replies to this question: which means that either no one knows, no one read it, no one cares or if was a stupid question.

I'll try my luck in another Forum but if you who are reading this now do have a suggestion please let rip.

Thanks

 

[tunsarod]

An Access2000 continuous form gets its data from a Select query whose datasource is a table in an AS400 database.

Without any criteria in the query all the records display in the subform. Now if the query is modified to include in a date field a criteria referencing the textbox like so:
[Forms]![myForm]![myTextbox]
where myTextbox contains a date value, no records are displayed even when the textbox date matches the date field in the data table. The date field in the table which is being compared to the textbox on the form is a TEXT type not a DATE type.

When this is used to pull records from an apparently similar table within the Access database it works fine.

Can anyone tell me whether the Access/AS400 connection requires a different syntax in the query's criteria?

Rod

 

[RMTnut]

I don't know if you got your answer or not, but it has been sometime that I worked with Access.

1) I would format the data from that AS400 table to a date type that Access can read. Test that and see if that works.

If is does than 2) I would set the subform to responsed to an AFTERUPDATE on the last field of the main form. That way it will only bring in that data that meets the criteria of the query set by your calendar control.

Hope this is some help