Query problem with form (1 Viewer)

JudyHNM · Dec 4, 2017

I have created a query which sorts a field that is a combination of text values and numbers (from double digits to four digits). The purpose of the query is to sort the values so the text fields are at the top and the remainder of the numbers are in true numerical order (rather than the default order). It works perfectly when I run the query. However, I created a form that used the query for the data and it prompts me to enter parameter values for 2 of the 3 fields I had to create in the query to make the sort work correctly. I just hit enter for both of the prompts, and then it works, but I don't want it to show the prompts. How do I suppress them? I have attached a file that shows the fields I added to the query to make this sort work.

Thanks, Judy

moke123 · Dec 4, 2017

Something looks wrong with the image you posted. Normally as far as I know if you type something into the Field box of a query which is not in the source table, an alias is applied (Like EXP1). i dont see that in your picture. Also Isnumeric() returns true/ false. is that what you want?

numbers are in true numerical order (rather than the default order)

Are your number numbers or text?

we probably need to see the whole query, if not a sample db with some sample records.

JudyHNM · Dec 4, 2017

I don't know how to attach the actual query. If you need that, I will need instructions, but here's the SQL version of the query if that helps:

Code:

SELECT tbl_DMCList.tblD_ID, tbl_DMCList.tblD_DMCNum, tbl_DMCList.tblD_Name, tbl_DMCList.tblD_OldDMCName, tbl_DMCList.tblD_DMCCol, tbl_DMCList.tblD_ColLoc, tbl_DMCList.tblD_Release, tbl_DMCList.tblD_Discontinued, tbl_DMCList.tblD_ColorSub, tbl_DMCList.tblD_Inventory, tbl_DMCList.tbld_Reformulated, tbl_DMCList.tblD_Location, tbl_DMCList.tblD_EmbroideryThread, tbl_DMCList.[tblD_Perle115-3], tbl_DMCList.[tblD_Perle115-5], tbl_DMCList.[tblD_Perle116-5], tbl_DMCList.[tblD_Perle116-8], tbl_DMCList.[tblD_Perle116-12], tbl_DMCList.tblD_Variegated
FROM tbl_DMCList
ORDER BY IsNumeric(tbl_DMCList.tblD_DMCNum) DESC , Val(tbl_DMCList.tblD_DMCNum), tbl_DMCList.tblD_DMCNum;

JudyHNM · Dec 5, 2017

I have attached a stripped down version of the database. The field that has the numbers I want to be sorted is defined as text because it contains some alphanumeric characters. If you run qry_DMCList NumberSort, you will see the results as I want them shown, without any prompts for the parameter. The alphanumeric fields are at the top, followed by the numbers in "true" numerical number.

If you open the form fmnu_DMCDisplayChoice and click on the command button, it will open the form that is supposed to display the DMC numbers as they are shown in the query, BUT it prompts for two of the fields that I had to add to the query to force the sort. I just hit enter twice (at the parameter prompts) and the form display with the records in the correct order. I just don't want the prompt to display.

Thanks, Judy

Galaxiom · Dec 5, 2017

moke123 said:
Normally as far as I know if you type something into the Field box of a query which is not in the source table, an alias is applied (Like EXP1)..

The alias is not added if the Show box is unticked first.

Judy: You have not attached your database.

JudyHNM · Dec 5, 2017

Guess I can't upload the database. I can't get the file size small enough. Any tips would be appreciated. I have never tried this before.

Thanks, Judy

Galaxiom · Dec 5, 2017

Remove any unnecessary objects then Compact & Repair.

You can zip it too, to make it even smaller.

JudyHNM · Dec 5, 2017

I finally figured out how to get the database small enough to attach. Sorry to be such a doofus.

Thanks, Judy

JHB · Dec 5, 2017

JudyHNM said:
..
.. BUT it prompts for two of the fields that I had to add to the query to force the sort. I just hit enter twice (at the parameter prompts) and the form display with the records in the correct order. I just don't want the prompt to display.

It is because you've set the property "Order By On Load" to yes, and in the property "Order By" you refer to the table from where the data comes and not to the recordsource for the form + the fields isn't in the recordsource for the form.
Database attached.
Only info: When you want to attached a file here, do a "Compact & Repair" and then zip it, it'll reduce the size.

arnelgp · Dec 5, 2017

check if this is what you want.

JudyHNM · Dec 5, 2017

Thank you to the folks that helped with uploading the database and solving the problem. I really appreciate it. Obviously, I am somewhat new to Access and was completely stumped. Thank you again. arnelap -- the database you sent does exactly what I wanted and JHB, thanks for pointing out the specifics of what I did wrong. I have a lot to learn!

Judy

JudyHNM · Dec 5, 2017

arnelgp -- I noted your changes to my query also. It looks a lot more elegant than what I did, but I still need to spend some time working my way through it to completely understand it.

Thanks again -- Judy

Query problem with form (1 Viewer)

JudyHNM

Registered User.

Attachments

moke123

AWF VIP

JudyHNM

Registered User.

JudyHNM

Registered User.

Galaxiom

Super Moderator

JudyHNM

Registered User.

Galaxiom

Super Moderator

JudyHNM

Registered User.

Attachments

JHB

Have been here a while

Attachments

arnelgp

..forever waiting... waiting for jellybean!

Attachments

JudyHNM

Registered User.

JudyHNM

Registered User.

Similar threads

Users who are viewing this thread