By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Custom Filter Form

P: n/a
A2003, XP Pro.

I'm in the middle of designing a form to act as a custom filter, like a
very basic 'filter by form'. It has two sets of combos, those on the
left are to choose the field name(s) on which to filter, those on the
right to choose the value(s) from the chosen field(s). The idea is to
build up a dynamic SQL string on which to filter the recordset.

I thought I'd be able to overcome the data type issue by using CStr() in
the target form's query so that all fields are handled as text. My
question is, is there a way of using code to determine the data type of
the chosen field so that I can code in quotation marks (or not)
depending on what the data type is?

I hope that's fairly clear ;-)

Many thanks for your help.
Keith.
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> My question is, is there a way of using code to determine the data
type of
the chosen field so that I can code in quotation marks (or not)
depending on what the data type is?
Ola K:

If you have DAO referenced in code, you can retrieve the DataType
property from the desired .Field in the Form's bound .Recordset object:

<uncompiled code>

Select Case Me.Recordset.Fields("myFieldName").Properties("Typ e")
Case 3: 'Long
Case 10: 'String
End Select

</uncompiled code>

a value of 10 is a text string, 3 is a long integer etc:

See the online Visual Basic help for the 'Type Property' topic for the
full list of returned datatype values.

King Ron of Chi

Keith wrote: A2003, XP Pro.

I'm in the middle of designing a form to act as a custom filter, like a very basic 'filter by form'. It has two sets of combos, those on the left are to choose the field name(s) on which to filter, those on the right to choose the value(s) from the chosen field(s). The idea is to build up a dynamic SQL string on which to filter the recordset.

I thought I'd be able to overcome the data type issue by using CStr() in the target form's query so that all fields are handled as text. My
question is, is there a way of using code to determine the data type of the chosen field so that I can code in quotation marks (or not)
depending on what the data type is?

I hope that's fairly clear ;-)

Many thanks for your help.
Keith.


Nov 13 '05 #2

P: n/a
Many thanks King Ron, that's just what I wanted :o)

Regards,
Keith.

King Ron wrote:
My question is, is there a way of using code to determine the data


type of
the chosen field so that I can code in quotation marks (or not)
depending on what the data type is?

Ola K:

If you have DAO referenced in code, you can retrieve the DataType
property from the desired .Field in the Form's bound .Recordset object:

<uncompiled code>

Select Case Me.Recordset.Fields("myFieldName").Properties("Typ e")
Case 3: 'Long
Case 10: 'String
End Select

</uncompiled code>

a value of 10 is a text string, 3 is a long integer etc:

See the online Visual Basic help for the 'Type Property' topic for the
full list of returned datatype values.

King Ron of Chi

Keith wrote:
A2003, XP Pro.

I'm in the middle of designing a form to act as a custom filter, like


a
very basic 'filter by form'. It has two sets of combos, those on the


left are to choose the field name(s) on which to filter, those on the


right to choose the value(s) from the chosen field(s). The idea is


to
build up a dynamic SQL string on which to filter the recordset.

I thought I'd be able to overcome the data type issue by using CStr()


in
the target form's query so that all fields are handled as text. My
question is, is there a way of using code to determine the data type


of
the chosen field so that I can code in quotation marks (or not)
depending on what the data type is?

I hope that's fairly clear ;-)

Many thanks for your help.
Keith.


Nov 13 '05 #3

P: n/a
"King Ron" <Ki***************@covad.net> wrote:
If you have DAO referenced in code, you can retrieve the DataType
property from the desired .Field in the Form's bound .Recordset object:

<uncompiled code>

Select Case Me.Recordset.Fields("myFieldName").Properties("Typ e")
Case 3: 'Long
Case 10: 'String
End Select

</uncompiled code>

a value of 10 is a text string, 3 is a long integer etc:

See the online Visual Basic help for the 'Type Property' topic for the
full list of returned datatype values.

King Ron of Chi


OK Ron, sorry to be a bit thick on this but could you post the syntax I'd
need to use to get the field data type from a combo box that has it's Row
Source Type set to 'Field List'?

Many thanks again for your help.

Regards,
Keith.
Nov 13 '05 #4

P: n/a
Keith <ke*********@AwayWithYerCrap.com> wrote:
OK Ron, sorry to be a bit thick on this but could you post the syntax
I'd need to use to get the field data type from a combo box that has
it's Row Source Type set to 'Field List'?


Doesn't matter, got it sussed :o)
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.