Switched to SQL, few questions.

[zgray]

We converted our Access back end to a SQL server and lost some functionality.

Before switch to SQL we could right click on a column heading in a subform or datasheet and have a list with checkboxes of things to filter by. This is gone after the switch and would like to know how to get it back.

Another is that (I asked this before an got no reply) the date fields are all formatted yyyy-mm-dd. I have tried several different ways to make it the short date format in our front end. The two ways that worked are: using format(DateField, "Short Date") in a query but I cant change the date, the second is just doing vba me.date = format(me.date, "Short Date") which formats it right and I can change it. But I would have to write this code for every date on every form. There has to be a better way of doing this. My google searches pull up "select convert(varchar(11), getdate(), 101)" but they never tell me where to put the code.

Last is that the date picker for all my dates are gone. It works fine on unbound date fields but not the ones that are in my tables.

 

[mdlueck]

My google searches pull up "select convert(varchar(11), getdate(), 101)" but they never tell me where to put the code.

That goes into a Pass-Through SQL query. That code returns a string (varchar) representation of the date formatted to preferences "101".

 

[zgray]

That goes into a Pass-Through SQL query. That code returns a string (varchar) representation of the date formatted to preferences "101".

I made a pass through query using that and I wasn't able to edit any data with it. I googled it and read that it's read only data.

As for not being able to get the right click filter options on subforms and whatnot. It was just a setting that had to be checked in the option menu.

 

[mdlueck]

I made a pass through query using that and I wasn't able to edit any data with it. I googled it and read that it's read only data.

SELECT queries need not be anything besides read only. INSERT / UPDATE are queries that may not be read only.

Were you being sneaky having a Form issue an editable SELECT?

 

[zgray]

I just figured it out. My data type in the sql server was "date" I changed it to "datetime" and refreshed the linked tables and its now in short date format with the date picker.