forms based queries

please help i am new to sql and my old access 2000 program used forms based queries.

the user simply input the patients last name and first name and the form would open up every record that had a matching combo to edit and if the combo didnt exist the form was ready for new input.

im under the understanding that sql doesnt support forms based queries and no i would like to know how to open my access form but first prompt users for what last name and first name they are looking for then fill the recordset with just those patients.

i have upsized my access 2000. mdb file to an access project now adp.
so im not using linked tables anymore or this would not be a problem.

this is very urgent for me to fix . i have already fixed all my vb code to make my chart number automatically. now im stuck on getting a prompt for a user to enter info to fill a form.Try using a separe form that is Modal = yes. This forces them to close the form before opening the next form. Then on event Form.Close check to make sure variables (names) are set properly (not empty).

Formatting Question

I have an old DB2 app that has date values in 4 fields (i.e. Date1_MO, Date1_DA, Date1_CN, Date1_YR). I have several MSAccess queries that I convert this to a date by doing the following:

CDate(Date1_MO & "/" & Date1_DA & "/" & Date1_CN & Format(Date1_YR,"00"))

Piece of cake...however I am struggling with this in MSSQL.

Mostly I am fighting formatting the Year. As you can see, If I were to concatinate the above values i would come up with something like 3/9/204 for a date of March 9, 2004. (Each field is a numeric value).

I have gotten this far...

select CAST(Date1_MO as varchar(2))+ '/' + CAST(Date1_DA as varchar(2))
+ '/' + CAST(Date1_CN as varchar(2))+ CAST(Date1_YR as varchar(2)) as Date1
From tPrices

I still need to convert the whole string to a date, but more importantly, I cannot figure out how to get the last element (Year) to format as '04' instead of '4'. I can't concatinate a 0 in front of it for obvious reasons. (Athough I was tempted, just joking)

I looked through a lot of the T-SQL docs but have come up dry.

Anyway HELP!!!!!!Try this for the last 2 digits of the year:

right('0'+CAST(Date1_YR as varchar(2)), 2)|||Came to the same conclusion about the same time you replied...

Just playing with the conversion now.

Thanks for your response...

Formatting Query Data

When I run queries against tables in MSSQL2000 it returns dates with a time component and has way too many zeros after the decimal point.

Is there any functions or something I can put in the query so my data comes back with only a date for the date and only two decimal points for the numbers?You are generally best off leaving the formatting to the code that receives the data from the database. But you can use the Convert function to format a date and the round function for numeric values.

SELECT Convert(varchar,DateField,101),Round(NumericField,2)
FROM YourTable

However, Round may not work quite as you expect. It will round the value but not necessarily format it for display as you might expect. Other date formats are available, check Sql Server Books OnLine under the convert function for the options.|||I agree with McMurdo, however, that the best place to handle this is outside of the database, in your code that is displaying the data.|||I agree, this is what I wanted - the ability to have the data formatted correctly from SQL. I tried for ages to find a way to do that on the basis of what I new about MySQL (which has a lot of functions for this).

But I'll look into it, as ideally I want my data coming in right from the SQL Query, rather than .Net doing it. Indeed, there is another thread in another forum arguing that as people kept giving me the whole Eval solution.|||Can you strip trailing spaces as well - I have a CUSTNMBR that is 5 characters long put it pads it with four spaces to to make it 9 (the length of the field).

I'll investigate the day, so I may have the answer this morning (UK Time).|||Use RTRIM or LTRIM to strip spaces.sql