Table Field Properties
- Thread starter agehoops
- Start date
MarkK
bit cruncher
- Local time
- Today, 06:39
- Joined
- Mar 17, 2004
- Messages
- 8,472
If you use DAO the Field object exposes a Type property. It's a long integer, but the enumerated constants are listed in the DAO.DataTypeEnum in your object browser.
Code might look something like...
Probably you can do the same sort of thing with a TableDef.Fields(index) item.
Code might look something like...
Code:
dim rst as dao.recordset
set rst = CurrentDb.OpenRecordset("SELECT field1 FROM ....")
if rst.fields("field1").type = dbSingle then
'do something
end ifMarkK
bit cruncher
- Local time
- Today, 06:39
- Joined
- Mar 17, 2004
- Messages
- 8,472
It's not relevant HOW you identify and return a DAO.Field object, but it will be a member of the Fields collection of a TableDef, QueryDef, Or Recordset. Once you've done so, the field exposes a Type property. You might also explore the TypeName(<variable>) function, which returns a string that is the name of the data type of <variable>. Consider the following code.
Code:
dim rst as dao.recordset
dim fld as dao.field
set rst = CurrentDb.OpenRecordset("SELECT idField, cstr(idField) As strIdField FROM ....")
for each fld in rst.fields
debug.print fld.name, fld.type, typename(fld.value)
next fldMarkK
bit cruncher
- Local time
- Today, 06:39
- Joined
- Mar 17, 2004
- Messages
- 8,472
Have you worked with recordsets, querydefs or tabledefs in code before? If not, I'm not sure I can help you without writing a tome, but here's a fast & dirty overview...
1) The SQL selects fields and records from a table.
2) The recordset is the fundamental structure for manipulating records and fields, and using SQL, it provides data access to rows and columns selected from a table. The data you might see in a bound form are provided by a recordset.
3) The For Each...Next loop traverses the Fields collection of the Recordset.
4) The Debug.Print line shows you information about selected properties of each field, as per your original question.
Is this helping?
1) The SQL selects fields and records from a table.
2) The recordset is the fundamental structure for manipulating records and fields, and using SQL, it provides data access to rows and columns selected from a table. The data you might see in a bound form are provided by a recordset.
3) The For Each...Next loop traverses the Fields collection of the Recordset.
4) The Debug.Print line shows you information about selected properties of each field, as per your original question.
Is this helping?
Ok, so what exactly do i need to be using in order to get the information i need.
The example code you gave has an SQL string, but this won't always be for the same table or field, it will constantly be changing.
Basically, i've got one drop down box where the user chooses the table or query they want to run a report from. Then a list box below list all the fields in that table or query. The user then chooses up to 6 fields they want to appear. Then if they want to specify any criteria they can select them from a second list box. When they then click create report, they are prompted to enter the criteria they opted to, and it then creates the needed SQL string and alters a report to fit the new criteria. This works perfectly for any criteria that is text, however the moment the data type of the field is a number or a date or such like, it doesn't work as the SQL query created contains ' ' around the criteria, which throws back an error when it is a number value. So I'm trying to get it to find out the data type of the field first, then, if it is a number, generate the SQL without the '' and if it is a string, generate it with the ''
Either, is there a simpler way of doing this? OR could you help me code this
Thanks
The example code you gave has an SQL string, but this won't always be for the same table or field, it will constantly be changing.
Basically, i've got one drop down box where the user chooses the table or query they want to run a report from. Then a list box below list all the fields in that table or query. The user then chooses up to 6 fields they want to appear. Then if they want to specify any criteria they can select them from a second list box. When they then click create report, they are prompted to enter the criteria they opted to, and it then creates the needed SQL string and alters a report to fit the new criteria. This works perfectly for any criteria that is text, however the moment the data type of the field is a number or a date or such like, it doesn't work as the SQL query created contains ' ' around the criteria, which throws back an error when it is a number value. So I'm trying to get it to find out the data type of the field first, then, if it is a number, generate the SQL without the '' and if it is a string, generate it with the ''
Either, is there a simpler way of doing this? OR could you help me code this
Thanks
Where is that Data type being stored though? Because if i make a field in the table to stored it, it is just storing another bit of information for the record not each heading, so would I not need to still look up the data type to store it?
I'm just using the AddToList function I think
I'm just using the AddToList function I think
ok got it. Just created a new reference table which holds all the field names of data types that aren't text and it is then looked up in the code to find out what it is.
One last problem though, is that when the code is generated, to search for a date of birth or any date for that matter, I have to type it in, in the US format. Is there a way of doing it in the UK format??
One last problem though, is that when the code is generated, to search for a date of birth or any date for that matter, I have to type it in, in the US format. Is there a way of doing it in the UK format??
