Can't believe it - Back Again Already! - How to set variables to 'Type' in SQL Statement?

[Kahuna]

Hi Folks

Following on from last nights disaster <lol> (Doug, John and Bas know what I
mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from a
form to show which field to update in the table, a second input from the
form which says update where 'x' is the current table entry, and a third
input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when there
is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant find
the way to set the SQL variables to assume the data types of the field.
Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

The variable txtUpdate_With is actually now Dim'd as a variant, thought this
would allow it to assume the data type of the field to update to.

Guess: Is it possible to set the form field to accept only a
particular data type?
Is it possible to set the variable to a specific data type
before adding it to the SQL statement?
I need to remove the extra quotes around the
txtUpdate_With variable dependant upon the data type -
This last seems most likely - but does that mean
building the SQL on the fly or creating multiple copies of it (one for each
type)?

Help appreciated.

TIA

 

[Wayne Morgan]

Open the query in design mode. Go to the Query menu and choose Parameters...
Enter the parameter(s) exactly as you have it in the query and choose a data
type for the parameter. In the SQL, this will look like:

PARAMETERS [Minimum] DateTime, [Maximum] DateTime;
SELECT Table3.Field1, Table3.field2
FROM Table3
WHERE (((Table3.Field1)>=[Minimum] And (Table3.Field1)<=[Maximum]));

The PARAMETERS line is what gets added.

As far as limiting the entries in the form, you could use an Input Mask, the
BeforeUpdate event of the control to verify its value, bind the control to a
field in a table (the data type will match the field), check the data
type/value in your code before you get to the "strSQLUpdate =" statement.

 

[Kahuna]

Wayne - thanks for the feedback - but unless I haven't grasped your meaning
I don't think I can do what you suggest.

Open the query in design mode. Go to the Query menu and choose
Parameters... Enter the parameter(s) exactly as you have it in the query
and choose a data type for the parameter. In the SQL, this will look like:

PARAMETERS [Minimum] DateTime, [Maximum] DateTime;
SELECT Table3.Field1, Table3.field2
FROM Table3
WHERE (((Table3.Field1)>=[Minimum] And (Table3.Field1)<=[Maximum]));

The PARAMETERS line is what gets added.

The query is not a saved query by run with 'Docmd.RunSQL' the nature of the
query stops me binding the form to a table (the table field is not defined
until run time).

The data type for the parameter / variable must also change at run time.

As far as limiting the entries in the form, you could use an Input Mask,
the BeforeUpdate event of the control to verify its value, bind the
control to a field in a table (the data type will match the field), check
the data type/value in your code before you get to the "strSQLUpdate ="
statement.

Dont know if that makes any sense at all Wanye - probably missed your point
altogether did I?


--
Kahuna
------------

--
Wayne Morgan
MS Access MVP

Hi Folks

Following on from last nights disaster <lol> (Doug, John and Bas know
what I mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from
a form to show which field to update in the table, a second input from
the form which says update where 'x' is the current table entry, and a
third input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when
there is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant
find the way to set the SQL variables to assume the data types of the
field. Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

The variable txtUpdate_With is actually now Dim'd as a variant, thought
this would allow it to assume the data type of the field to update to.

Guess: Is it possible to set the form field to accept only a
particular data type?
Is it possible to set the variable to a specific data
type before adding it to the SQL statement?
I need to remove the extra quotes around the
txtUpdate_With variable dependant upon the data type -
This last seems most likely - but does that mean
building the SQL on the fly or creating multiple copies of it (one for
each type)?

Help appreciated.

TIA

 

[Bas Cost Budde]

Hi Folks

Following on from last nights disaster <lol> (Doug, John and Bas know what I
mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from a
form to show which field to update in the table, a second input from the
form which says update where 'x' is the current table entry, and a third
input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when there
is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant find
the way to set the SQL variables to assume the data types of the field.
Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

I expected this one to come.

Run from the Debug window:

?BuildCriteria("blnTest",dbBoolean,"True")
blnTest=True

So, you will wrap all your WHERE conjuncts inside the BuildCriteria,
forget about quotes, and go. Let me repeat the above statement, for example:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
BuildCriteria(txtField_to_Update,itsfieldtype,txtUpdate_With) _
& " WHERE (qryFabricCondition." &
BuildCriteria(txtField_to_Update,itsfieldtype,txt_Where_equal_to) & ")"

Aha! I forgot to tackle the field type. Hmm, that's why I maintain my
own data dictionary. But, you say you know it? Good! Supply it at the
'itsfieldtype' then.

You could possibly derive it from the database itself, using

currentdb.tabledefs("qryFabricCondition").Fields(txtField_to_Update).type

adapt the syntax as needed

 

[Marshall Barton]

Following on from last nights disaster <lol> (Doug, John and Bas know what I
mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from a
form to show which field to update in the table, a second input from the
form which says update where 'x' is the current table entry, and a third
input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when there
is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant find
the way to set the SQL variables to assume the data types of the field.
Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

The variable txtUpdate_With is actually now Dim'd as a variant, thought this
would allow it to assume the data type of the field to update to.

Guess: Is it possible to set the form field to accept only a
particular data type?
Is it possible to set the variable to a specific data type
before adding it to the SQL statement?
I need to remove the extra quotes around the
txtUpdate_With variable dependant upon the data type -
This last seems most likely - but does that mean
building the SQL on the fly or creating multiple copies of it (one for each
type)?

You're already building the SQL on the fly. What you need
to do is take the type of the field to bu updated into
account. Here's some air code that should demonstrate the
idea:

strSQLUpdate = "UPDATE qryFabricCondition " _
& "SET " & [txtField_to_Update] & " = " _
If <type of field is Text> Then
strSQLUpdate = strSQLUpdate _
& "'" & txtUpdate_With & "'" _
& " WHERE " & [txtField_to_Update] _
& " = '" & txt_Where_equal_to & "'"
ElseIf <type of field is date> Then
strSQLUpdate = strSQLUpdate _
& Format(txtUpdate_With, "\#m\/d\/yyyy\#") _
& " WHERE " & [txtField_to_Update] _
& " = " & Format(txt_Where_equal_to, "\#m\/d\/yyyy\#")
Else 'number type
strSQLUpdate = strSQLUpdate & txtUpdate_With _
& " WHERE " & [txtField_to_Update] _
& " = " & txt_Where_equal_to
End If

 

[Kahuna]

That's exactly what I need Marshal thanks - just couldn't get my head around
the FORMAT action/method.

I'll try that tonight thanks a lot.

--
Kahuna
------------

Following on from last nights disaster <lol> (Doug, John and Bas know what
I
mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from a
form to show which field to update in the table, a second input from the
form which says update where 'x' is the current table entry, and a third
input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when
there
is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant
find
the way to set the SQL variables to assume the data types of the field.
Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

The variable txtUpdate_With is actually now Dim'd as a variant, thought
this
would allow it to assume the data type of the field to update to.

Guess: Is it possible to set the form field to accept only a
particular data type?
Is it possible to set the variable to a specific data
type
before adding it to the SQL statement?
I need to remove the extra quotes around the
txtUpdate_With variable dependant upon the data type -
This last seems most likely - but does that mean
building the SQL on the fly or creating multiple copies of it (one for
each
type)?

You're already building the SQL on the fly. What you need
to do is take the type of the field to bu updated into
account. Here's some air code that should demonstrate the
idea:

strSQLUpdate = "UPDATE qryFabricCondition " _
& "SET " & [txtField_to_Update] & " = " _
If <type of field is Text> Then
strSQLUpdate = strSQLUpdate _
& "'" & txtUpdate_With & "'" _
& " WHERE " & [txtField_to_Update] _
& " = '" & txt_Where_equal_to & "'"
ElseIf <type of field is date> Then
strSQLUpdate = strSQLUpdate _
& Format(txtUpdate_With, "\#m\/d\/yyyy\#") _
& " WHERE " & [txtField_to_Update] _
& " = " & Format(txt_Where_equal_to, "\#m\/d\/yyyy\#")
Else 'number type
strSQLUpdate = strSQLUpdate & txtUpdate_With _
& " WHERE " & [txtField_to_Update] _
& " = " & txt_Where_equal_to
End If

 

[Kahuna]

Thanks Bas - not got around to trying yours or the other suggestion yet.

I do have a data dictionary for all of the specified fields, and I have them
recorded as the numerical type of the data ie 1= Yes/No 3 = Number 8 =
Date/Time.

Can these numeric representations be used in your BuildCriteria string Bas?

I've not come across the BuildCriteria before - is this a built in function?

Cheers

--
Kahuna
------------

Hi Folks

Following on from last nights disaster <lol> (Doug, John and Bas know
what I mean) - I need some guidance on this one:

I have an SQL Update statement (that works!!), which takes an input from
a form to show which field to update in the table, a second input from
the form which says update where 'x' is the current table entry, and a
third input where the user types in what he'd like the data changed to.

All works lovely in a text field in the table but fails dismally when
there is a number / bool / date etc.

I know the data type number of the field chosen to be updated, but cant
find the way to set the SQL variables to assume the data types of the
field. Here's the SQL string:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
[txtField_to_Update] & " = " & "'" & txtUpdate_With & "'" & "" _
& " WHERE (qryFabricCondition." &
[txtField_to_Update] & "= " & "'" & txt_Where_equal_to & "'" & ")"

I expected this one to come.

Run from the Debug window:

?BuildCriteria("blnTest",dbBoolean,"True")
blnTest=True

So, you will wrap all your WHERE conjuncts inside the BuildCriteria,
forget about quotes, and go. Let me repeat the above statement, for
example:

strSQLUpdate = "UPDATE qryFabricCondition SET qryFabricCondition." &
BuildCriteria(txtField_to_Update,itsfieldtype,txtUpdate_With) _
& " WHERE (qryFabricCondition." &
BuildCriteria(txtField_to_Update,itsfieldtype,txt_Where_equal_to) & ")"

Aha! I forgot to tackle the field type. Hmm, that's why I maintain my own
data dictionary. But, you say you know it? Good! Supply it at the
'itsfieldtype' then.

You could possibly derive it from the database itself, using

currentdb.tabledefs("qryFabricCondition").Fields(txtField_to_Update).type

adapt the syntax as needed
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

 

[Bas Cost Budde]

Thanks Bas - not got around to trying yours or the other suggestion yet.

I do have a data dictionary for all of the specified fields, and I have them
recorded as the numerical type of the data ie 1= Yes/No 3 = Number 8 =
Date/Time.

Can these numeric representations be used in your BuildCriteria string Bas?

I've not come across the BuildCriteria before - is this a built in function?

At least in A97, yes, it's built in. To my embarrasment I found out
*after* I wrote my own.

The second argument is numerical indeed. I use symbolical constants
where I can, but on that place I don't need to in my code. You don't
need to either, if you can pluck it out of your DD. It's nice working
with a DD, right?

 

[Kahuna]

--
Kahuna
------------

At least in A97, yes, it's built in. To my embarrasment I found out
*after* I wrote my own.

The second argument is numerical indeed. I use symbolical constants where
I can, but on that place I don't need to in my code. You don't need to
either, if you can pluck it out of your DD. It's nice working with a DD,
right?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

Yep sure is Bas - just updated my DD to include the full field type. Makes
life a lot simpler - though I have to say - after having some 3 months away
from the keyboard - some of my most basic skills have drifted. Must be years
since I had to post such simple requests here. Still great to know we have
guys like you supporting us 'chumps'.

Thanks again my friend.