Parameter type is wrong

[smbrr]

Hello,

I'm making a fairly complex application. At some point, I open a querydef and cycle through its parameters to prompt the user to enter it.

Then, according to the type of the parameter, I will put tokens around it (" for string, # for dates, nothing for numbers), and put the parameters in another SQL with the replace() function.

It's pretty convoluted so let's keep it at that. My issue is that for a "test query", I put a random parameter on a random field (a number field, long integer) but the querydef.parameters(index).type is dbText

I figured out that I can force the type by putting it in the query's parameter button in the ribbon on top in design mode, but no user is ever going to do that when making their queries.

Is this a bug? Is there an easy workaround?

Thanks

 

[KenHigg]

Why not just build a form for the user to put thier params in?

 

[arnelgp]

form would be suitable for this situation where there are number of parameters.

 

[gemma-the-husky]

I would also make sure that your date parameters evaluate correctly, if you do not use US date formats.

 

[spikepl]

Instead of a story, show what you are doing. Because

Then, according to the type of the parameter, I will put tokens around it (" for string, # for dates, nothing for numbers)

is weird. If you have parameters for a querydef then the above normally is not done/necessary.

 

[smbrr]

Nope, I can't have parameters in a form because that module handles any query the user creates, it's entirely dynamic. There could be 0 parameters, there could be 100. I also don't want the user to have to list them before starting the module, as that would become a chore.

The SQL i manipulate is NOT the querydef, it's a different SQL built from different things.

The context doesn't matter, my question is about querydef.parameters(index).type

Why does it return dbText type when the field is a Number field in the table (table the query is based upon). Is there a way to get the correct type without having to fetch the sourcefield (getting complicated in case of nested selects) or other complex SQL parsing?

Thanks

 

[KenHigg]

This still seems like a bad way to build an end user query tool. So... how would the query param object know what data fld type it is to be used for before you use it?

 

[Uncle Gizmo]

Well I don't understand your question at all, however I thought it worth mentioning that I note MS Access tends to put quote marks around values automatically. I wonder if it is putting quote marks around your integer values without you knowing?

 

[smbrr]

So... how would the query param object know what data fld type it is to be used for before you use it?

How would it NOT know? It's a querydef object, saved in the application. It's a query based on a table, it should know all the fields and what type they are.
When I start the query manually by double-clicking it, it prompts me for my [parameter], I give it a string and it converts it in whatever type it needs (in this case a number). So it does know what type it is.

It's notable to say that I can just give it anything and it works, if I'm just trying to execute it, for example:

For Each p In qd.Parameters
    p.Value = InputBox(p.Name)
Next p
Set rs = qd.OpenRecordset

That works very well, but is not what I'm trying to do:

For Each p In qd.Parameters
    xh_(ui).plist_(p.Name).Type = p.Type
Next p

Will fill my list with "dbText" type parameters.

The application knows the type of the parameter, so why?