Date Parameter Question

Hi all,
I am still kind of new at this...
I am trying to have a date parameter that the user selects a month and it
brings back all the data for that month. Say the users picks August, the
report comes back for all the data for the month of August.
I hope I explained it well enough.
Any help would be great.
Thanks in advance,
KerrieConsider creating a parameter (called @.Month for example) and enter in the
"Available Values" (Report -> Report Parameters) the following:
Jan 1
Feb 2
Mar 3
etc, for each month. Then in your query use the following on your date
column:
SELECT
*
FROM
tblData
WHERE
DATEPART(mm, dateStart) = @.Month
to explain:
DATEPART(mm, <date>) returns the month of a date, e.g. for the 16th of July
it would return "7" for July.
Hope that helps,
-Geoff R G Williams
Primal Blaze Ltd.
"KS" <KS@.discussions.microsoft.com> wrote in message
news:303428AC-348D-45C1-BBCD-9EC5C032AEAE@.microsoft.com...
> Hi all,
> I am still kind of new at this...
> I am trying to have a date parameter that the user selects a month and it
> brings back all the data for that month. Say the users picks August, the
> report comes back for all the data for the month of August.
> I hope I explained it well enough.
> Any help would be great.
> Thanks in advance,
> Kerrie
>
>

Date format problem

I am using a view which brings purchase related data from my OLTP.

I am using the same view for the source of my production system (which has a mm/dd/yy format) and for my test machine (which has a dd-Mmm-yy format).

By formats I mean theformatsdefinedvia theRegional settings.

The problem is that on the test machine, the dates are giving problems and a date such as today's (i.e. 10, March, 2005) is coming in as 03-October, 2005) - notice that this is due to the data format difference i.e. 03/10/05 versus 10-Mar-05 (or 10/03/05).

Any ideas how I can use the same view but for my test machine I do some sort of conversion to correctly change the format.

Many TIAHi all

I've faced the same issue here, our company uses DD/MM/YYYY date format and one of the computers we had uses MM/DD/YYYY.
to come around this problem we had to think like MS-SQL server. our application must use YYYY-MM-DD so we wrote a function in VB to read the current user date format and change the dates to SQL server format.

to use this function just pass the date and the result will be YYYY-MM-DD.
you can use this function before inserting, updateting or deleting data in VB.


please have fun.:D



Public Function fDate(ByVal xDateIn As String) As String
Dim xReg As New Registry
Dim xRegDateFormat As String
Dim xRegDateSeperator As String
Dim xDay, xMonth, xYear As String
Dim xTmp As Integer
Dim xTmpStr As String


If xDateIn = "" Then Exit Function

xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SSHORTDATE", xRegDateFormat
xReg.GetKeyValue HKEY_CURRENT_USER, "Control Panel\INTERNATIONAL", "SDATE", xRegDateSeperator

xDateIn = Trim(Str(CDate(xDateIn)))

xRegDateFormat = UCase(xRegDateFormat)

If InStr(1, xRegDateFormat, "DD") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "D")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "D" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "MM") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "M")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "M" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

If InStr(1, xRegDateFormat, "YYYY") <> 0 Then
Else
xTmp = InStr(1, xRegDateFormat, "YY")
xRegDateFormat = Left(xRegDateFormat, xTmp) + "YY" + Right(xRegDateFormat, Len(xRegDateFormat) - xTmp)
End If

'step 01
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 02
Select Case Left(xRegDateFormat, 1)
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = Left(xDateIn, xTmp - 1)
If xDay < 10 And Len(xDay) = 1 Then
xDay = "0" & xDay
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = Left(xDateIn, xTmp - 1)
If xMonth < 10 And Len(xMonth) = 1 Then
xMonth = "0" & xMonth
xDateIn = "0" & xDateIn
xTmp = xTmp + 1
End If
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = Left(xDateIn, xTmp - 1)
If xYear < 100 Then
xYear = "20" & xYear
xDateIn = "20" & xDateIn
xTmp = xTmp + 1
End If
End Select
xDateIn = Mid(xDateIn, xTmp + 1)
xRegDateFormat = Mid(xRegDateFormat, xTmp + 1)

'step 03
Select Case UCase(Left(xRegDateFormat, 1))
Case "D"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xDay = xDateIn
If xDay < "10" And Len(xDay) = 1 Then xDay = "0" & xDay
Case "M"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xMonth = xDateIn
If xMonth < "10" And Len(xMonth) = 1 Then xMonth = "0" & xMonth
Case "Y"
xTmp = InStr(1, xDateIn, xRegDateSeperator)
xYear = xDateIn
If xYear < "100" Then xYear = "20" & xYear
End Select

'fDate = xDay + "/" + xMonth + "/" + xYear
fDate = xYear + "/" + xMonth + "/" + xDay

End Function|||Thanks but there's got to be a simpler solution that this :(|||yes, the simple solution is to use the CONVERT function|||The other simple solution is just to format the date when it is sent through initially.|||yes, the simple solution is to use the CONVERT function

Wish I got paid by the line...

Date conversion and formatting

Hi all,
Need some help. I have a report which brings back data over a period
of time from one field (date_required). This is all returned in one
column. I would like to create a report whereby the data is filtered
into the appropriate Month's column, jan, feb etc etc.
I have used the Month statement to extract this and tried using the
visibility expression to create this but without any joy.
Could someone give me some pointers on how to achieve this?
Thanks
GaryHere's what I did. In the query, use the datepart(MM,datefield) to extra the
month number, 1-12. Then in the matrix use the sorting tab of the group
properites to sort ascending. This will sort the columns 1 --> 12. Then go
into the properties for the month field and set the expession to something
like this:
=Left(MONTHNAME(Fields!MonthField.Value),3)
the columns will show Jan, Feb, Mar,etc. rather the 1,2,3...
Hope this helps you.
"gdavid9@.hotmail.com" wrote:
> Hi all,
> Need some help. I have a report which brings back data over a period
> of time from one field (date_required). This is all returned in one
> column. I would like to create a report whereby the data is filtered
> into the appropriate Month's column, jan, feb etc etc.
> I have used the Month statement to extract this and tried using the
> visibility expression to create this but without any joy.
> Could someone give me some pointers on how to achieve this?
> Thanks
> Gary
>