Hej!

Jag har en fet databas i access som jag vill göra så att man kan visa med en rapport i access. Men det är två saker jag inte får till:

1. Jag vill kunna ta fram bara några poster. Alltså få rapporten att fatta en WHERE namn<>''.
2. Jag vill göra om ett tal t.ex. 210 till hela timmar och minuter. Så att 210 blir 03:30.

Någon som kan hjälpa mig?

mvh Jakob Hägg

<b>1. Jag vill kunna ta fram bara några poster. Alltså få rapporten att fatta en WHERE namn<>''.</b>

WHERE namn Is Not Null

<b>2. Jag vill göra om ett tal t.ex. 210 till hela timmar och minuter. Så att 210 blir 03:30.</b>

=DateAdd("n",[FältMedMinuter],0)

<b>WHERE namn Is Not Null</b>

Vart skriver man in det? Jag har letat som en galning efter sql frågan.

<b>=DateAdd("n",[FältMedMinuter],0)</b>

"Uttryckets syntax är ogiltlig" fick jag på den?

<b>Vart skriver man in det? Jag har letat som en galning efter sql frågan.</b>

Egenskapen datakälla på formuläret.

<b>"Uttryckets syntax är ogiltlig" fick jag på den?</b>

Testa med:
=DateAdd("n";[FältMedMinuter];0)

Tänk på att kontrolen INTE får heta samma som fältet!

<b>Testa med:
=DateAdd("n";[FältMedMinuter];0)

Tänk på att kontrolen INTE får heta samma som fältet!
</b>

Så i mitt fall skriver jag =DatumLäggTill("n";[borjar];0)???
När jag skriver det så skriver han bara ut #Namn?

Finns [borjar] med i din SQL fråga? Finns det någon kontroll med namnet borjar?

Alltså jag har inget formulär så jag har inte fått fram själva sql frågan... Men det fungerar att bara skriva ut borjar...

Hi,
your secound question

how to get 210 MINUTES presented as hrs + minutes, not so simple, first it can't be done with out you writing a user defeined function. As it is only presentation your after I guess you could use something like..

SELECT System.Element, [Data]/60 AS Hrs
FROM System
WHERE (((System.Element)="TM"));

so your 210 ( str or numeric value divied by 60 = 3.5

Would this be good enough?

If not you'll have to make a quick UDF and use it in your sql command.

As to your question number one....
If using acc 200 you can have this code snippet, it should guide you how to use a function to open a report invisible and set the runtime sql ( where clause will be decided at runtime right?)
and then save and close the report and then open it in acviewnormal or acviewpreview.

Read the code first then ask me questions, if you have any...
It has a lot more than you need but you should be able to cut n paste your way to a working sub
'******************************************start
Public Function PrintReport(ByVal Prefix As String, ByVal TheReportNameToPrint As String, LstClients As ListBox, frm As Form, ByVal AllowPrint As Boolean, Optional ByVal theSQl As String) As Byte
'*****************************
'Author Paul Horsley
'Date 2001-12-18
'time : 14:22
' ****************************
' function has 5 parameters
' 0 = the string name of the report to print
' 1 = lstclients = a listbox
' 2 = the calling form
' 3 = AllowPrint = should print or preview
' 4 = the sql string to use // Optional
' ****************************
' Function will return a long indicating print results
' 0 = All done OK
' > 0 the VBA error code. use raise back at calling function to get description.
'
'*****************************
On Error GoTo Print_Err

Dim intCount, iCurrentItem, svar As Integer
Dim strfilter, TMPAccount, stDocName, ExpAccount As String
Dim vntItem As Variant
Dim dbmyDB As Database
Dim DocLoop As Document
'SQL STRINGS AVAILABLE
Dim myA4Sql As String
Dim myexportSql As String
Dim mysql As String
' The final sql string to use as record source in whatever report you ahve made.
' You have three sql string being made all the time for you
' in the reports table record you assign a numeric 1,2,3 to the sqltype field and
' this will be used for passing one of the sql's made here into the final sql string >> local variable strRecordSourceSql
' The strRecordSourceSql will be used to set the reocrd source of the report.
Dim strRecordSourceSql As String
Dim iResult As Long
iResult = 0
Dim repExists As Boolean

'old stuff not needed but maybe you have a use?
If IsEmpty(theSQl) Then
iResult = 1
'GoTo Print_exit
End If

Set dbmyDB = CurrentDb
With dbmyDB.Containers!Reports
For Each DocLoop In .Documents
If DocLoop.Name = TheReportNameToPrint Then
repExists = True
Exit For
End If
Next DocLoop
End With
'set object to nothing - good house cleaning + free mem
Set dbmyDB = Nothing

If Not repExists Then
MsgBox "The requested report " & TheReportNameToPrint & " is not available in this version of Consol2000," & vbCr & "contact support and inform them.", vbOKOnly + vbInformation, "Missing report"
iResult = 0
GoTo Print_exit
End If

intCount = LstClients.ItemsSelected.Count - 1

If intCount = -1 Then
MsgBox "To create a report you must first choose an EDI-CON client batch from the list provided.", vbOKOnly + vbInformation, "No batch selected"
LstClients.Requery
frm.frmReports.Value = 0
Exit Function
Else
If intCount = 0 Then
For Each vntItem In LstClients.ItemsSelected
strfilter = strfilter & "((([010-calculations_archive].repSource)=" & _
"'" & LstClients.ItemData(vntItem) & "'" & "))" & " OR " '"
Next
If strfilter <> "" Then
strfilter = Left(strfilter, Len(strfilter) - 4)
LstClients.Requery
mysql = "SELECT [010-calculations_archive].* FROM [010-calculations_archive] WHERE " & strfilter & ";"
myexportSql = "SELECT [010-calculations_archive].* INTO TmpConExp FROM [010-calculations_archive] WHERE " & strfilter & ";"
myA4Sql = "SELECT [010-calculations_archive].repSource , [010-calculations_archive].ISEU FROM [010-calculations_archive] GROUP BY [010-calculations_archive].repSource , [010-calculations_archive].ISEU HAVING " & strfilter & ";"
Else
MsgBox "No items selected", vbOKOnly
LstClients.Requery
frm.frmReports.Value = 0
Exit Function
End If
Else
For Each vntItem In LstClients.ItemsSelected
strfilter = strfilter & "([010-calculations_archive].repSource)=" & _
"'" & LstClients.ItemData(vntItem) & "'" & " OR "
Next
If strfilter <> "" Then
strfilter = Left(strfilter, Len(strfilter) - 4) & ")"
myexportSql = "SELECT [010-calculations_archive].* INTO TmpConExp FROM [010-calculations_archive] WHERE ((" & strfilter & ");"
mysql = "SELECT [010-calculations_archive].* FROM [010-calculations_archive] WHERE ((" & strfilter & ");"
myA4Sql = "SELECT [010-calculations_archive].repSource ,[010-calculations_archive].ISEU FROM [010-calculations_archive] GROUP BY [010-calculations_archive].repSource , [010-calculations_archive].ISEU HAVING ((" & strfilter & ");"
Else
MsgBox "No items selected", vbOKOnly
LstClients.Requery
frm.frmReports.Value = 0
Exit Function
End If
End If
End If

'use the GetSqlType function to return the sqltype from the reports record in question.
Select Case GetSqlType(Prefix & TheReportNameToPrint)
Case 1
strRecordSourceSql = mysql
Case 2
strRecordSourceSql = myexportSql
Case 3
strRecordSourceSql = myA4Sql
Case Else
'if not found or > than 1 the function will exit garcefully ( no error given as we assign 0 to function as result )
iResult = 0
GoTo Print_exit
End Select
'set string vars to "" - free mem
mysql = ""
myexportSql = ""
myA4Sql = ""

'This outershell is used to find out if we should strRecordSourceSql
If GetSqlUsage(Prefix & TheReportNameToPrint) = 1 Then
' Use the documents container to grab the report we need.
' you can not use any other method as only open reports are seen in the reports collection
' To get passed this I used the documents container of the object dbmydb, and assigned
' the contianer as !Reports - works great!!!
Set dbmyDB = CurrentDb
With dbmyDB.Containers!Reports
For Each DocLoop In .Documents
If DocLoop.Name = TheReportNameToPrint Then
DoCmd.Echo False, "Creating " & DocLoop.Name & "report - please wait."
DoCmd.OpenReport DocLoop.Name, acViewDesign
Reports(DocLoop.Name).RecordSource = strRecordSourceSql
DoCmd.Close acReport, DocLoop.Name, acSaveYes
DoCmd.Echo True
Exit For
End If
Next DocLoop
End With
'set object to nothing - good house cleaning + free mem
Set dbmyDB = Nothing
End If
' finally we see if the param passed indicates to print or to show preview ' Slick!!!
If AllowPrint Then
DoCmd.OpenReport TheReportNameToPrint, acNormal
Else
DoCmd.OpenReport TheReportNameToPrint, acPreview
End If

Print_exit:
' first pass off the iresult value to the function, then exit
PrintReport = iResult
Exit Function

Print_Err:
'here you can make your own error capture descisions
Select Case Err.number
Case 0
Case Else
iResult = Err.number
End Select
Resume Print_exit

End Function

'************************ END CODE SNIPPET

hope this helps, cheers Paul