number format in sql queries

I was about to post a question yesterday, but I found a solution myself and would like to share it. When a new column is computed in the query, there is no way to prescribe the data format of the computed column (=field =variable). It is done automatically by access and it can go wrong. I tried to use Format() function but it changes the look but does not change the type of the column. So what you have to do is to wrap your expression in a data convertion function. I used CDBL() as a wrapper for my expression.

I consider it as a bug of Access because in my situation I had a swich() function that took only numeric fields and produced only number but the data type of the column was set to TEXT. I noticed it in the pivot table later on , when I could only use Count() as aggregate function.

Good luck! and take care of data conversion by yourself, do not let it go automatically!

P.S. If anyone knows a better way to prevent such conversion errors, be my guest!!!

By
Maxim Ivashkov
http://www.4suc6.com


Post your answer or comment

comments powered by Disqus
Is it possible to change the format of a field that is output via a SQL query? I have a union query that that counts days from a certain date to now(). I then want to use the min function to find number of days (lowest number)since the most recent date. The problem I have is that the query outputs a field in text format and the min function dosen't work since it uses the first characters regardless of length to determine the lowest number, i.e. 1889 comes before 19. I've tried outputing the query to a table and re-querying the table but don't know how to change the format in the table. If there is a more efficient way to accomplish this please let me know. I'm new to this so I hope my explanation is sufficient. Thanks to all who repond.

I am trying to convert a date field in sql to a DD:MM format
example
right now its 01-16-2007 and i want to convert it to 01-16 and forget the year at the end

any help?

again this is for SQL Query im working on in SSMS 2005

Hello all,

I use SQL to populate several listboxes on a form and the number format for one of the columns isn't passing from the table to the listbox correctly. Is there was way in SQL to define the format for that field?

Here is an example of the statement that I have right now:
str = "SELECT MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number], MasterBacklog.[Aging Bucket], " _
& "MasterBacklog.[Order Age], MasterBacklog.[Backlog Value], MasterBacklog.[Product ID] FROM [MasterBacklog]" _
& "WHERE (((MasterBacklog.[LEVEL 3])like '*' & '" & industryLST & "' & '*') AND ((MasterBacklog.[Deferred Revenue]) Is Null))ORDER BY MasterBacklog.[Master Customer Name], MasterBacklog.[Order Number] ;"

I would like the Backlog Value to show in the standard format with 2 decimal places. Thanks for help! I always award reputation points. Thanks

Brennan

I just upgraded to Access 2010. Previous version was 2003. I have a query which formated data so the decimal place were all equal length:

CS Fee: Format([fee rate],"0.00000")

Worked good in 2003 but the data sheet will not display in 2010 and gives the following error message "undefined function 'Format' in expression". Not sure how to resolve. I have a number of these types of decimal formats in numerous queries.

I've read a few posts about date formatting in SQL, but I can't seem to get it right. I have an update query but the final date it inputs into the table is 30-Dec-1899. The format is right but the date is not.

I have a pop-up form where I enter the date (the format is fine in both forms) and then it updates the dates on all selected records in the main form. Everything works fine, except it takes a long time to update (even just 2 records) and the date is wrong. Here's the code:

	Code:
	Private Sub cmdUpdate_Click()
DoCmd.SetWarnings False
Dim cnn1 As ADODB.Connection
    Set cnn1 = CurrentProject.Connection
            
    Dim rs1 As New ADODB.Recordset
    rs1.ActiveConnection = cnn1
      
    Dim SQL1 As String
    Dim DX1 As Date
    DX1 = Format(Forms!frmMark!txtDatePrep, "Medium")
    
    SQL1 = "UPDATE tblDNA_Kit_Prep SET tblDNA_Kit_Prep.[Select] = False," & _
            " tblDNA_Kit_Prep.DatePrepared = " & Forms!frmMark!txtDatePrep & "," & _
            " tblDNA_Kit_Prep.PreparedBy_ID = " & [Forms]![frmMark]![cboSampler] & "," & _
            " tblDNA_Kit_Prep.Status = 'Prepared'" & _
            " WHERE tblDNA_Kit_Prep.[Select] = True"
    
    rs1.Open SQL1
    cnn1.Close
    Set rs1 = Nothing
    Set cnn1 = Nothing
    
DoCmd.SetWarnings True
DoCmd.Close acForm, "frmMark", acSaveNo
End Sub

Any help would be much appreciated!
Erica

PS. Also, I can't seem to run the code line by line, it just beeps at me when I press F8, and when I select 'Step Into'. What am I doing wrong (I keep just going back to the form and hitting the command button, driving me nuts).

I have a booking system and it all works fantastically apart from the dates being stored in the form dd/mm/yyyy, except if the date is before the 13th, then it is stored mm/dd/yy.

I want all dates stored as dd/mm/yyyy but i have just read in the help files that any dates in SQL code must be usa format, but not in the query designer.

I have come up with a few solutions but I'm not sure if any are actually doable. I have tried a search but didn't find any answers.

So here's my ideas:

1. Pass the variables to a query in access

2. Insert the data without using sql?

3. Insert the dates number into the table and format it in my forms

If anyones got any other ideas, please let me know, I'm desperate!

Here's my code:


	Code:
	Private Sub add_booking_Click()

Dim dteBookDate As Date
Dim dteLeaveDate As Date
Dim nightnum As Integer

dteBookDate = CDate(Forms("booking2").date1)
dteLeaveDate = CDate(Forms("booking2").date2)
nightnum = Forms("booking2").nights

DoCmd.RunSQL "INSERT INTO booking VALUES (" & Forms("booking2").[room_no] _
                  & ", #" & Format(dteBookDate, "dd/mm/yyyy") & "#, " & Forms("booking2").[Cust_no] & ", " & False & ", " &
nightnum & " );"
dteBookDate = DateAdd("d", 1, dteBookDate)

Do While dteBookDate < dteLeaveDate
   DoCmd.RunSQL "INSERT INTO booking VALUES (" & Forms("booking2").[room_no] _
                  & ", #" & Format(dteBookDate, "dd/mm/yyyy") & "#, " & Forms("booking2").[Cust_no] & ", " & False & "," & 0
& ");"
   dteBookDate = DateAdd("d", 1, dteBookDate)
Loop
DoCmd.RunMacro "book_room"
End Sub

Thanks
Emma

I am having a problem formatting some numbers in a UNION query.

I have two simple queries which return basic scores and their average for a specific period. I have each query rounds the averages so I don't get any decimals (EX 9.3333=9). When I use a UNION query to get the two separate queries together, it seems to be ignoring the formatting of the original queries and shows the decimal numbers (EX 9.3333).

How do I format the UNION query to retain the rounded number (on export as well)?

Is it possible to set a default so that any number that is generated in a query, either by count, sum or calculation is automatically formatted to the standard format?

When attempting to run a Delete query via VBA based on a combobox, I'm getting the following error:

"Object does not support this property or method"

I'm unable to find anything via search that says this is, or is not possible.

I currently have a simple form, with a combobox and a command button. The combobox is based on a query to select and group the filenames stored within a table. The user selects one of these filenames, and uses the command button. The command button runs a Delete query which should delete the records from the table. The error is all I get. When the delete query is run in SQL I get the correct results, but when I attempt to represent that string in VBA I think I may be losing something, or framing it incorrectly.

SQL Version from a regular query that I attempted to represent in VBA:


	Code:
	Delete *
FROM tblDrillStudyData
WHERE (((tblDrillStudyData.FileName)=[Forms].[frmRecordDelete].[cboFileName]));

VBA Function attached to command button:


	Code:
	Private Sub cmdDelRecSet_Click()
On Error GoTo Err_cmdDelRecSet_Click
    Dim strQuery As String
    
    strQuery = "DELETE * FROM [tblDrillStudyData] WHERE(((tblDrillStudyData.FileName) = '" &
[Forms].[frmRecordDelete].[cboFileName] & "'));"
    DoCmd.RunSQL strQuery

Exit_cmdDelRecSet_Click:
    Exit Sub

Err_cmdDelRecSet_Click:
    msgbox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_cmdDelRecSet_Click
    
End Sub

I also attempted to create a text box on the form and link it as a text string from the combobox, but I get the same results. Any Ideas?

Some of my queries get a bit long and I need to do a lot of cut/paste in the SQL view. To help with this I like to format the code to make it easier to read. However, when I come back the next day the formatting is lost and everything is all bunched-up.

Does anyone know how to save the formatting in the SQL view?

hi,
i created a database in access with forms and querires, and it works well.
now i want this to be moved to sql, i tried using the upsizing wizard, and i could move th tables properly. the problem is with the queries. i have used the vaules of the form fileds with in the queries to itrait the number of records returned by the queries.
eg
select name from customer where(customer.name= forms![customer search].name);
when i moved this query to sql, it gives me error. it looks like i cannot use the field names of the forms within sql query statement.
please help me to solve this problem
all i want to know is how to use the vaules(eg value in a text box) that i enter in a form with in a sql query.
i am waiting for a response

I have a table with a three digit number field that I query and concatenate with another field to display an ID. However, the three digit format is lost during the query if there a leading zeros in the number. How can I maintain the three digit format during the concatenation?

Hi,

Im trying to combine 2 fields below into one column.

ID - 001 (This is formatted in table using 000)

Sometext - "BLAH"

In the query I have written this ...

Newfield: [Sometext] & [ID]

But when I preview the query the data displays as "BLAH1" and not "BLAH001"

what should i do to correct this formatting issue?

many thanks!

I've created a report with a chart that includes a data table at the bottom of the chart. I would like the data table entries to be in Currency format with no decimals.

I've opened the datasheet for the chart in chart design view, selected the appropriate cells, and changed the number format.

When I view the report (and the chart) in design view, the data table entries appear in the correct format. However, when I view the report in Print Preview, the number format reverts to how it was before I made the changes.

Is there something I'm missing? Thanks for any help you can provide on this!!!

Hello,

I am working with charts in reports, in the designview I see in the charttable '2,24%' but when I turn to reportview I see 2,24E-02 ??

I think I have set the number format and 2 decimals correctly, still I do not see the desired result. Another problem is with the EURO sign, in designview I see amounts correctly for example '284,254 E' but when I again turn to reportview I see only '284254'.

Can somebody please help me out?

Thanks!

Hi I have a macro to export a query to CSV format by "ImportExportText". My original number field in the query have 4 decimal places. After the export, I see the numbers are in 2 decimal places in CSV file. Is it a way to change the query or some settings to show 4 decimal places in the output CSV file? Any help will be appreciated.

I have been trying to use the SQL view in a query to append multiple rows to a table. I have had no luck. Any suggestions?

Thank you.

I'm trying to display a number in the first field of a query for each row in the query starting with the number 3 and increment the number by 1 for each additional row. I've seen queries of running totals but what would I total on?
Any help is appreciated.
Scott

I've got some numbers in a table that are formatted as currency, which I'm unable to change. I change the format in my query and that works(changes it to a simple number) but when I export that query to an excel file, the format is changed again to a currency. This exporting is a constant process and is one that overwrites the excel file each time it is done. So how can I get the format to stay simple numerical throughout the export process?

I would like to create a seperate field for each record in my query which would number the results of the query in ascending order, say from 1 to 100.
Is it possible to do this in a query?

Hi,

I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.

Thanks,

Jeff

How do I number the lines in a query? I have a 'Top 50' query, and I want to number the lines 1 through 50.

Maybe a simple question, but i do not know how to put the current date in the WHERE clause of a SQL query.

The query i have so far:


	Code:
	select [table1].*, [table2].*  from [table1], [table2] where [table1].[field] = [table2].[field] and [table1].[enddate] is
null

After this there has to be something like

	Code:
	and [table1].[begindate] < [Date] (where [date] is the expression)

I know that with Date() or Now() the expression can be made, but i do not know how, please advise..

Thanks in advance

hi All

I need to have an auto number in an access query, this is not the auto number already existing in the table. I need a separate auto number field in the query which begins with 1 and is in increasing order.
Any help will be much appreciated


Not finding an answer? Try a Google search.