convert value format in query Results


Page 1 of 6.
Results 1...20 of 104

Sponsored Links:



Hi All.
In my calculation in a query:
Cost: Sum([Cost]/12*(DateDiff("m",[StartDate],Now())))
Cost is Currency;
StartDate is Date.

The result look like Numeric format I would like to have Currency format. Is it possible?
Thanks.




Hi, I'm trying to convert Null values from a query upon which my report is based into '0'. I noted on this forum a posting that suggested I go into the query and field properties and put 0;0;0;0 in Format to force the input to be 0 for neg, pos, 0, or null values... but this hasn't worked.

I also tried the =Nz([fieldname],0) and it came up with an error message.

How do I make sure that the fields that aren't returning any value appear as 0? (Or more precisely, 0!!)

Any ideas?




I am getting this error everytime I run this script. Can anyone tells what is causing the error?
To give you a brief description of my database -

I have got a database that consists of a query which calculates monthly inventory data (MIOH) A cross tab query then creates a cross tab such that employee number is the row heading, month (mm/yyyy) , YTD (yyyy) are column headings and MIOH is the value. There are two parameters in this query which are entered by the user using a form. These are start date and end date. The SQL of the cross tab query is given below.

CrossTab query SQL

	Code:
	TRANSFORM Sum(Union.METRIC) AS SumOfMETRIC
SELECT Union.ABUYR, Union.TYPE
FROM [Union]
WHERE (((Union.PERIOD1) Between [Forms].[Form1]![StartDate] And [Forms].[Form1]![EndDate]))
GROUP BY Union.ABUYR, Union.TYPE
PIVOT Union.PERIOD1; 



The code I am using is:

	Code:
	Option Compare Database
'  Constant for maximum number of columns CrossTab query would
    Const conTotalColumns = 14
'  Variables for Database object and Recordset.
    Dim dbsReport As DAO.Database
    Dim rstReport As DAO.Recordset
'  Variables for number of columns and row and report totals.
    Dim intColumnCount As Integer
    Dim lngRgColumnTotal(1 To conTotalColumns) As Long
    Dim lngReportTotal As Long
Private Sub InitVars()
Dim intX As Integer
' Initialize lngReportTotal variable.
    lngReportTotal = 0
' Initialize array that stores column totals.
    For intX = 1 To conTotalColumns
    lngRgColumnTotal(intX) = 0
Next intX
End Sub
Private Function xtabCnulls(varX As Variant)
' Test if a value is null.
    If IsNull(varX) Then
' If varX is null, set varX to 0.
        xtabCnulls = 0
    Else
' Otherwise, return varX.
        xtabCnulls = varX
    End If
End Function
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    ' Put values in text boxes and hide unused text boxes.
    Dim intX As Integer
    '  Verify that you are not at end of recordset.
        If Not rstReport.EOF Then
    '  If FormatCount is 1, put values from recordset into text boxes
    '  in "Detail" section.
            If Me.FormatCount = 1 Then
            For intX = 1 To intColumnCount
    '  Convert Null values to 0.
            Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
        Next intX
    '  Hide unused text boxes in the "Detail" section.
        For intX = intColumnCount + 2 To conTotalColumns
            Me("Col" + Format(intX)).Visible = False
        Next intX
    '  Move to next record in recordset.
        rstReport.MoveNext
            End If
        End If
End Sub
Private Sub Detail_Retreat()
' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious
End Sub
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
    Dim intX As Integer
'  Put column headings into text boxes in page header.
        For intX = 1 To intColumnCount
        Me("Head" + Format(intX)) = rstReport(intX).Name
        Next intX
'  Hide unused text boxes in page header.
    For intX = (intColumnCount + 1) To conTotalColumns
    Me("Head" + Format(intX)).Visible = False
    Next intX
End Sub
Private Sub Report_Close()
On Error Resume Next
'  Close recordset.
rstReport.Close
End Sub
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
'  Create underlying recordset for report using criteria entered in
'  Form1 form.
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
'  Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!Form1
'  Open QueryDef object.
Set qdf = dbsReport.QueryDefs("CrossTab")
' Set parameters for query based on values entered
' in Form1 form.
qdf.Parameters("[Forms].[Form1]![StartDate]") _
= frm!StartDate
qdf.Parameters("[Forms].[Form1]![EndDate]") _
= frm!EndDate
'  Open Recordset object.
Set rstReport = qdf.OpenRecordset()
'  Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
End Sub
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
'  Move to first record in recordset at the beginning of the report
'  or when the report is restarted. (A report is restarted when
'  you print a report from Print Preview window, or when you return
'  to a previous page while previewing.)
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub

I get error 3265 - item not found in collection and the debugger highlights the line


	Code:
	Me("Head" + Format(intX)) = rstReport(intX).Name

When I hover my mouse over the rstReport(intX).Name part, the pop up shows "rstReport(intX).Name =
Any help is appreciated!
Thanks!




I created a report for a Crosstab Query based on the instructions provided by Microsoft knowledge base. I have no problem running this report in Access 2007 but when I try to run it in Access 2003 I get this error:

"The expression On Open you entered as the event property setting producted the following error: Invalid procedure call or argument
* the expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]
* There may have been an error evaluating the function, event, or macro "

Here is the code behind the report:

Option Compare Database

' Constant for maximum number of columns query would
' create plus 1 for a Totals column. Here, you have a 12 month report + 1 for total=13.
Const conTotalColumns = 41


' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset

' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub InitVars()

Dim intX As Integer

' Initialize lngReportTotal variable.
lngReportTotal = 0

' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX

End Sub


Private Function xtabCnulls(varX As Variant)

' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If

End Function


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.

Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX

' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX

' Move to next record in recordset.
rstReport.MoveNext
End If
End If

End Sub


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer
Dim lngRowTotal As Long

' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0

For intX = 2 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))

' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX

' Put row total in text box in the "Detail" section.
Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
' Add row total for current row to grand total.
lngReportTotal = lngReportTotal + lngRowTotal
End If
End Sub


Private Sub Detail_Retreat()

' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious

End Sub


Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)

Dim intX As Integer

' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX

' Make next available text box Totals heading.
Me("Head" + Format(intColumnCount + 1)) = "Totals"

' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub Report_Close()

On Error Resume Next

' Close recordset.
rstReport.Close

End Sub


Private Sub Report_NoData(Cancel As Integer)

MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True

End Sub


Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.

Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms![Main Menu]
' Open QueryDef object
Set qdf = dbsReport.QueryDefs("SYS_QRY_CSI_AllWeeks_Crosstab _Email")
' Set parameters for query based on values entered
' in Report Dialog Box form.
qdf.Parameters("Forms![Main Menu]![RCSD]") = frm!RCSD
qdf.Parameters("Forms![Main Menu]![RCED]") = frm!RCED
qdf.Parameters("Forms![Main Menu]![RCAM]") = frm!RCAM


' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

End Sub


Private Sub ReportFooter_Print(Cancel As Integer, PrintCount As Integer)

Dim intX As Integer

' Put column totals in text boxes in report footer.
' Start at column 2 (first text box with crosstab value).
For intX = 2 To intColumnCount
Me("Tot" + Format(intX)) = lngRgColumnTotal(intX)
Next intX

' Put grand total in text box in report footer.
Me("Tot" + Format(intColumnCount + 1)) = lngReportTotal

' Hide unused text boxes in report footer.
For intX = intColumnCount + 2 To conTotalColumns
Me("Tot" + Format(intX)).Visible = False
Next intX

End Sub


Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst

'Initialize variables.
InitVars

End Sub


Any ideas?




Hi folks,

I'm a complete newbie with Access, but I have a great deal of experience in using VBA with Excel. However, I've realised that one of my Excel toys needs to be converted to Access owing to the volume of data it holds.

As such, yesterday I started to "play" with Access and built a couple of tables, queries and forms. On one of the forms I have a ComboBox (called startDate), I've added an after update event to this to trigger a macro to search for a record in a table called shiftTable, and return 3 pieces of data when it locates the record.

I built the query to do this using the Access query design which works correctly, and then grabbed the SQL to use in the macro, this is shown below.


	Code:
	SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatch
FROM shiftTable
WHERE (((shiftTable.activityDate)=activitydetail.startDate));

This is the macro that I've built around that SQL....


	Code:
	 
Private Sub startDate_AfterUpdate()
Dim ssqlCom As String
Dim stDate As Date
Me.End_Date = Me.startDate
stDate = Me.startDate
 
ssqlCom = "SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatch" & _
          "FROM shiftTable " & _
          "WHERE shiftTable.activityDate=#" & stDate & "#;"
 
DoCmd.RunSQL (ssqlCom)
End Sub

However, every time I run it I get a 3075 missing operator error (in this example I selected 27/01/2011 in the startDate ComboBox);

Quote: Syntax error(missing operator) in query expression
'shiftTable.nsWatchFROM shiftTable WHERE
shiftTable.activityDate=#27/01/2011#'. I've tried various things to fix it, including converting the date to US format, putting a space in front of the FROM command, declaring stDate as string instead of date. I'm getting frustrated now, especially as I suspect I'm missing something very obvious. All help appreciated!

EDIT:

As an experiment, I just chucked a msgBox in before the DoCmd statement to show me the value of ssqlCom, which returns this:

Quote: SELECT shiftTable.amWatch, shiftTable.pmWatch, shiftTable.nsWatchFROM shiftTable WHERE shiftTable.activityDate=#27/01/2011#; Thanks,

James.




Hello everyone

I have an issue with my database. The database works fine in 2003. However when I try run it in 2007, one of the reports based on a cross-tab query (that works fine in 2007) would not open and comes up with a 3070 error ("Microsoft Access Database engine does not recognize " as a valid field name or expression"). It does not specify a field but only ".

The interesting thing is that there are other two dynamic crosstab reports that do work just fine. I'm puzzled.

Query Code:

	Code:
	PARAMETERS [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] DateTime,
[Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate] DateTime;
TRANSFORM Sum([REPORT BY EMPLOYEE 3MTH OUT by Project].SumOfWorkload) AS SumOfSumOfWorkload
SELECT [REPORT BY EMPLOYEE 3MTH OUT by Project].Type AS Manager, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name],
[ProjectID] & " " & [Reference Name] AS Project
FROM [REPORT BY EMPLOYEE 3MTH OUT by Project] LEFT JOIN AccessImport ON [REPORT BY EMPLOYEE 3MTH OUT by Project].ProjectID =
AccessImport.[Project #]
WHERE ((([REPORT BY EMPLOYEE 3MTH OUT by Project].Week) Between [Forms]![EmployeeSalesDialogBoxProjectID]![BeginningDate] And
[Forms]![EmployeeSalesDialogBoxProjectID]![EndingDate]))
GROUP BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID]
& " " & [Reference Name]
ORDER BY [REPORT BY EMPLOYEE 3MTH OUT by Project].Type, [REPORT BY EMPLOYEE 3MTH OUT by Project].[Employee name], [ProjectID]
& " " & [Reference Name]
PIVOT [REPORT BY EMPLOYEE 3MTH OUT by Project].Week;


the vba fo cross tab:


	Code:
	' Constant for maximum number of columns EmployeeSales query would
' create plus 1 for a Totals column. Here, you have 9 employees.
Const conTotalColumns = 13
' Variables for Database object and Recordset.
Dim dbsReport As DAO.Database
Dim rstReport As DAO.Recordset
' Variables for number of columns and row and report totals.
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long
Private Sub InitVars()
 
Dim intX As Integer
' Initialize lngReportTotal variable.
lngReportTotal = 0
 
' Initialize array that stores column totals.
For intX = 1 To conTotalColumns
lngRgColumnTotal(intX) = 0
Next intX
End Sub
 
Private Function xtabCnulls(varX As Variant)
 
' Test if a value is null.
If IsNull(varX) Then
' If varX is null, set varX to 0.
xtabCnulls = 0
Else
' Otherwise, return varX.
xtabCnulls = varX
End If
End Function
 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
' Put values in text boxes and hide unused text boxes.
 
Dim intX As Integer
' Verify that you are not at end of recordset.
If Not rstReport.EOF Then
' If FormatCount is 1, put values from recordset into text boxes
' in "Detail" section.
If Me.FormatCount = 1 Then
For intX = 1 To intColumnCount
' Convert Null values to 0.
Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
Next intX
 
' Hide unused text boxes in the "Detail" section.
For intX = intColumnCount + 2 To conTotalColumns
Me("Col" + Format(intX)).Visible = False
Next intX
' Move to next record in recordset.
rstReport.MoveNext
End If
End If
 
End Sub
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
 
Dim intX As Integer
Dim lngRowTotal As Long
' If PrintCount is 1, initialize rowTotal variable.
' Add to column totals.
If Me.PrintCount = 1 Then
lngRowTotal = 0
 
For intX = 4 To intColumnCount
' Starting at column 2 (first text box with crosstab value),
' compute total for current row in the "Detail" section.
lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
' Add crosstab value to total for current column.
lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
Next intX
 
End If
End Sub
 
Private Sub Detail_Retreat()
' Always back up to previous record when "Detail" section retreats.
rstReport.MovePrevious
End Sub
 
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
 
Dim intX As Integer
 
' Put column headings into text boxes in page header.
For intX = 1 To intColumnCount
Me("Head" + Format(intX)) = rstReport(intX - 1).Name
Next intX
 
' Hide unused text boxes in page header.
For intX = (intColumnCount + 2) To conTotalColumns
Me("Head" + Format(intX)).Visible = False
Next intX
End Sub
 
Private Sub Report_Close()
 
On Error Resume Next
' Close recordset.
rstReport.Close
 
End Sub
 
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No records match the criteria you entered.", vbExclamation, "No Records Found"
rstReport.Close
Cancel = True
End Sub
Private Sub Report_Open(Cancel As Integer)
' Create underlying recordset for report using criteria entered in
' EmployeeSalesDialogBox form.
 
Dim intX As Integer
Dim qdf As QueryDef
Dim frm As Form
' Don't open report if EmployeeSalesDialogBox form isn't loaded.
If Not (IsLoaded("EmployeeSalesDialogBoxProjectID")) Then
Cancel = True
MsgBox "To preview or print this report, you must open " _
& "Date Period Dialog in Form view.", vbExclamation, _
"Must Open Dialog Box"
Exit Sub
End If
 
' Set database variable to current database.
Set dbsReport = CurrentDb
Set frm = Forms!EmployeeSalesDialogBoxProjectID
' Open QueryDef object.
Set qdf = dbsReport.QueryDefs("EmployeeSalesProjectID")
' Set parameters for query based on values entered
' in EmployeeSalesDialogBox form.
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!BeginningDate") _
= frm!BeginningDate
qdf.Parameters("Forms!EmployeeSalesDialogBoxProjectID!EndingDate") _
= frm!EndingDate
' Open Recordset object.
Set rstReport = qdf.OpenRecordset()
 
' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count
 
End Sub
 
 
Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
' Move to first record in recordset at the beginning of the report
' or when the report is restarted. (A report is restarted when
' you print a report from Print Preview window, or when you return
' to a previous page while previewing.)
rstReport.MoveFirst
'Initialize variables.
InitVars
End Sub

Any help will be appeciated!
Here's the cross tab query code:




Hi,

No doubt this is very simple to solve, but I can't find the solution after searching for ages!

I have a form where the user enters a date range, and then I want to search a query on that date range and return information to the user. An extract of the significant code follows:

StartDate = [Txt_Start]
EndDate = [Txt_End]

Set rst = db.OpenRecordset("SELECT * from [qry_ConversianRateSummaryQuotes&Ack] WHERE ((([qry_ConversianRateSummaryQuotes&Ack].[Quotation Sent?]) Between #" & StartDate & "# And #" & EndDate & "#))", dbOpenDynaset)

With this code I get back values from the wrong date range. I realise that I need to somehow convert the data taken from the form into the international date format for use in queries but can't for the life of me work out how to do this! Everything works fine if I hard code a date range rather than taking it from the form, so once I have sorted the conversion everything should be fine.

If anyone can help me out I would be very grateful!

Thanks in advance!




Hi,

I am having a problem on something that seems very simple.

I have a query with three fields [last name] [first name] and [dateofbirth]

I created a string field to uniquely identify each person
[firstname]&[lastname]&[dateofbirth]

My problem is that even though the [dateofbirth] field is formatted mm/dd/yyyy and it's value in the base table is 01/01/2001, it changes to 1/1/2001 in the string field.

The [dateofbirth] field in the query shows as 01/01/2003, but the string converts it to 1/1/2003. I need the full 10 digit date to link this to another table.

Is there a way to ensure the string expression keeps the exact format and # of digits ?

Thanks Chris




Hi all,

I have an inherited database with years worth of julian dates stored as numbers (e.g. days 1-366 for a leap year). I also have some fields stored as short dates. I would like to run a series of parameterized queries on this data, some using the short data and some using the "julian dates" that are actually just numbers. I have the user enter the start and end date in short date format and would like that to be converted and held in an unbound field as a number value. So far I can get the date to appear as a "julian date" (e.g. 1/1/08 appears as 1, obviously the underlying data is still 1/1/08) How do I take that 1 and convert it to number value "1"? Thanks in advance.

Cheers,
Peter




I am trying to write a query that will use the difference (in years) between 2 seasons which are recorded in 2 different tables in the format 2009/2010, 2010/2011, etc. (these tables get their values from a look-up table that contains all legitimate seasons).

How can I determine the difference numerically?
Can I convert this format, in my query, to a numerical format (say, 2010, 2011 or 100, 101, 102, etc) and then subtract?




I am currently attempting to import a CSV data file that lists information in a top down format that I need to convert to a compacted flat file form for further calculations and manipulation, but cannot figure out how to to do so. I believe I'm missing something basic in how I'm doing my select query, or I'm trying to make it do something it's not intended to do.

Examples attached as DumpedData.jpg before, and ConvertedData.jpg after conversion.

The common key is based on [Layout] & [Ring] & [Hole], with [Point Number] being the variable to combine on. And I'm pulling the X,Y, and Z from the second entry and appending it into the first record string, so I have a coordinate start and stop point by which I can generate calculations. Basically there should always be two lines in the data set with the first three fields the same, and the last field being two sequential numbers, though that's not always guaranteed, but one will always be larger than the other for sure. There are some entries in the data set where the first three fields will be the same for 25 records in a row, but that's something I can deal with on a separate select distinct query.

I've found concatenation routines on here, but that won't work for me as I need to maintain numeric integrity, in individual fields, and data separation in order to do calculations.

Any suggestions to point me in the right direction? Am I looking at some do/while loops in VBA, or is this something that I could somehow extract the leading half of the data with one query, the trailing half with a second query, and then union the final results back together in the order I need?




Hello. I see many similar topics but unfortunately I still am doing something wrong.

My date field is in text format and looks like this:
20121102 (yyyymmdd)

Here is what I've tried:
DATEVALUE(LEFT([FIELDNAME],4),MID([FIELDNAME],5,2),RIGHT([FIELDNAME],2))
Result: The expression you entered has a function containing the wrong number of arguments

DATEVALUE(LEFT([FIELDNAME],8))
Result: Enter parameter value

DATEVALUE(MID([FIELDNAME],5,2)&"/"&MID([FIELDNAME],7,2)&"/"&LEFT([FIELDNAME],4))
Result: Enter parameter value

The conversion works in Excel using this:
DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

We just updated our system which uses this date format in all files so I need to revise all of my existing queries as they are not working. Your help is appreciated!




I wounder if anyone PLEASE could help me with a
problem that I am facing with MS.Access query.

the problem is that I made a query a crosstable
query months in the rows and years in the columns. and parameters startdate and
enddate

so whene I do a query with a date rang between
[startdate] and [enddate]
it gives me as expected null values in some fields.
What I need now is to convert the null value to
zeros (only the null values within the date range), and to keep the null values out of the range as they
are.

the SQL statement is:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count(GNRLFRM.MRN) AS CountOfMRN
SELECT Format([OVADATE],"mmm") AS Expr1
FROM GNRLFRM
WHERE (((GNRLFRM.OVADATE) Between [StartDate] And
[endDate]))
GROUP BY (Month([OVADATE])*12+Month([OVADATE])-1),
Format([OVADATE],"mmm")
PIVOT (Year([OVADATE]));


Please Help me.
and thank you very much in advance.




Hi, I have created a table with a column called lastlogon, which stores the date of the last logon from windows active directory for a user id. Everything is being imported from a text file. Most of the time there is a valid date of format, "6/8/2006 1:10:23 PM" . I can declare the column as a Date/Time with that format, but sometimes if the user has never logged on, the source data will "never" for that column.

I have resorted to creating the column as a text field and trying to "convert" the valid fields to real dates for use in queries. In a very similar situation, I had the same problem with numbers and text. There is another column that keeps track of the password age, and it will either contain a number (in days) or the string, "NeverChanged". I was able to use the Val() function to get workable results. Is there something similar that can be done for dates to do an expression conversion but still store it as a text field so that I can accomodate for the occasional string, "never" ?

There are ~25,000 records that are being imported so I don't want to simply do a search and replace the "never" string with a bogus date.

Thanks for any help!




I am using CDate to convert a date text string into a date value, which is then listed in ascending order in the result.

If I leave the default format as 'dd/mm/yy' the data is in the correct date ascending order when the query runs but if I use -

Format(CDate(etc, etc),"dd mmm yy")

The query is in alpha/numeric order as if the value is a text string and not a date value.

Not entirely sure why it is doing this when all that I have changed/added is the 'Format' function.




I'm building a query (if you saw my other thread, this is not for that database, but one I need to track a program).

I've got a file in Excel format that I need to import roughly once a month. In this file, there is a field titled Pay Code in a A, B, C format. What I'm wanting to do, is I'm creating a merged column in the query that will merge the Pay Code with the persons skill code, but to try and simplify it so I'm not always going the Find and Replace route.

I've already gone through and done the Find and Replace and changed everything to how I want it for now, but I'd like to prevent this in the future.

Is there a way, probably not in a query, to automate the Find and Replace function so that if I have the values I want replaced and the values to replace the original value already established in a table I can just run a macro or something to change the fields?

Best example I can provide:

Skill Code | Pay Code
A | A

After Find/Replace function with A being converted to 1

Skill Code | Pay Code
A | 1

Merged in query
A1




Hi people,

I'm working on upgrading an old DOS Open Access database for a carpet cleaning company. All the fields were imported into MS Access fine, EXCEPT the time field. This was imported as a Long Integer number.

I've now found out how to convert the long integer into a valid number format in MS Access and have created a SELECT CASE code matching up the common long integer values with their equivalent time value. In other words, a TIME MATRIX!!

However, I don't know how to use this code to update my tables. I've thought I could use an UPDATE query, but I don't know how to implement the SELECT CASE statement into the query to provide the results I want.

I've tried to create a Function I can run in the Immediate window to update the table with the new values of Time. No luck so far.

Anyone had this problem before? You're welcome to use my Time Matrix if it helps.

Anyway, here's the code:


Listing 1.1
================================================== =======
Dim MyTime As Long

Select Case MyTime 'Update Time number to normal time
Case 0
MyTime = #12:00:00 AM#
Case 5460
MyTime = #1:15:00 AM#
Case 9120
MyTime = #2:15:00 AM#
Case 12780
MyTime = #3:30:00 AM#
Case 21960
MyTime = #6:00:00 AM#
Case 25620
MyTime = #7:00:00 AM#
Case 26520
MyTime = #7:15:00 AM#
Case 27420
MyTime = #7:30:00 AM#
Case 28320
MyTime = #7:45:00 AM#
Case 28620
MyTime = #7:50:00 AM#
Case 29280
MyTime = #8:00:00 AM#
Case 30180
MyTime = #8:15:00 AM#
Case 31080
MyTime = #8:30:00 AM#
Case 31980
MyTime = #8:45:00 AM#
Case 32940
MyTime = #9:00:00 AM#
Case 33840
MyTime = #9:15:00 AM#
Case 34140
MyTime = #9:20:00 AM#
Case 34740
MyTime = #9:30:00 AM#
Case 35640
MyTime = #9:45:00 AM#
Case 35940
MyTime = #9:50:00 AM#
Case 36600
MyTime = #10:00:00 AM#
Case 36900
MyTime = #10:05:00 AM#
Case 37500
MyTime = #10:15:00 AM#
Case 38400
MyTime = #10:30:00 AM#
Case 39300
MyTime = #10:45:00 AM#
Case 40260
MyTime = #11:00:00 AM#
Case 41160
MyTime = #11:15:00 AM#
Case 41460
MyTime = #11:20:00 AM#
Case 42060
MyTime = #11:30:00 AM#
Case 42660
MyTime = #11:40:00 AM#
Case 42960
MyTime = #11:45:00 AM#
Case 43260
MyTime = #11:50:00 AM#
Case 43560
MyTime = #11:55:00 AM#
Case 43800
MyTime = #11:59:00 AM#
Case 43920
MyTime = #12:00:00 PM#
Case 44520
MyTime = #12:10:00 PM#
Case 44820
MyTime = #12:15:00 PM#
Case 45720
MyTime = #12:30:00 PM#
Case 46620
MyTime = #12:45:00 PM#
Case 47580
MyTime = #1:00:00 PM#
Case 48480
MyTime = #1:15:00 PM#
Case 48780
MyTime = #1:20:00 PM#
Case 49380
MyTime = #1:30:00 PM#
Case 49500
MyTime = #1:35:00 PM#
Case 49680
MyTime = #1:40:00 PM#
Case 50280
MyTime = #1:45:00 PM#
Case 50580
MyTime = #1:50:00 PM#
Case 51240
MyTime = #2:00:00 PM#
Case 52140
MyTime = #2:15:00 PM#
Case 52440
MyTime = #2:20:00 PM#
Case 53040
MyTime = #2:30:00 PM#
Case 53940
MyTime = #2:45:00 PM#
Case 54900
MyTime = #3:00:00 PM#
Case 55200
MyTime = #3:05:00 PM#
Case 55500
MyTime = #3:10:00 PM#
Case 55800
MyTime = #3:15:00 PM#
Case 56700
MyTime = #3:30:00 PM#
Case 57000
MyTime = #3:35:00 PM#
Case 57600
MyTime = #3:45:00 PM#
Case 58560
MyTime = #4:00:00 PM#
Case 59160
MyTime = #4:10:00 PM#
Case 59460
MyTime = #4:15:00 PM#
Case 60060
MyTime = #4:25:00 PM#
Case 60360
MyTime = #4:30:00 PM#
Case 61260
MyTime = #4:45:00 PM#
Case 61560
MyTime = #4:59:00 PM#
Case 62220
MyTime = #5:00:00 PM#
Case 62820
MyTime = #5:10:00 PM#
Case 63120
MyTime = #5:15:00 PM#
Case 63420
MyTime = #5:20:00 PM#
Case 64020
MyTime = #5:30:00 PM#
Case 65880
MyTime = #6:00:00 PM#
Case 66780
MyTime = #6:15:00 PM#
Case 69540
MyTime = #7:00:00 PM#
Case 76860
MyTime = #9:15:00 PM#
Case 80520
MyTime = #10:15:00 PM#
Case 82320
MyTime = #10:45:00 PM#
Case 84180
MyTime = #11:15:00 PM#
Case 85980
MyTime = #11:45:00 PM#
'Case Else

End Select

================================================== =======




Good day to all,

I am new to coldfusion programming, but I have done some web development with it in the past 6 months or so. Here is my issue.

I build online queries so that users in other departments can run their reports without the need to use any applications like Acces or Crystal. One problem I find is that when the copy and paste the values into Excel. Regardless of the formatting they do in Excel, they always get the "8.22E+15" instead of "82231300005548" which is the correct account number. One thing they can do is use the paste special and then convert text to columns, but when you are working with monkeys, you really want to give them something easy.

Here is my question... Is there a format commend to force text into a field even if the value is all number? Below you can find my code. Thanks in advance for your help.





DVR Cleanup









Account:
Qty:
Effect:






#Accnt#
#qty#
#Effect#








Best Regards,
Emmanuel
WebManny.com




I have a database for converting settlement instruction formats between one system and another. When this was created for me, one of the columns that I needed to be in the query was not included. The table was created from an imported Excel file which is how the report is run. I need to have a formula where if the value in a column called PAY_INDICATOR is an "T" then to return the value in a column called PAY_ACCRU, if the PAY _INDICATOR column has an "O", then return the value that is in column PAY_BEN_PARTY. Otherwise, if there are no values provided in either of the columns the query would search depending on if we have a P or a Q, columns: (PAY_ACCRU or PAY_BEN_PARTY), then return the value "NULL". We have the below formula in the query already, however when we run the query it does not display the results or the column at all.

ULTBEN_ADD_LINE1(T58/T59): Iif([Banks_Comparison_Results].[Pay_Indicator]="T",[Banks_Comparison_Results].[PAY_ACCOUNT],[PAY_BEN_PARTY])

I dont see the criteria where if the value in column PAY_INDICATOR is an "O" how this is evaluated, or if no values are present in both columns display the word "NULL" Any help would be very appreciated! Thanks




Hello all,

I am writing a query that is pulling in data from a column labeled "Priority". What I need help with is one of the common values returned from this column is "Normal". What I need it to say is "Routine" where ever it comes in as "Normal" in my query results. I was looking at the 'Format' function but it seems to be only for dates. I cannot use an Update query because I don't have the rights to update this table.

I also use SQL developer and know about the to_char function but that also seems like it's only for converting date formats.

Does anyone know of a function that I could use to do this? If this is confusing I'll try to explain it a different way.

Thank you,
rjohnson


Page 1 of 6.
Results 1...20 of 104