export to excel (1 Viewer)

B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
hi, I try write vba for export tables to excel - and I deal with some problems;

1. for every recordset this line works well:
Code: 
WHERE [machinenum]='" & macs & "'
macs - it's string
and in the origin tables , machinenum is long

only 1 recordset "remember" it expects to long and not string and I received error...(I success fix it by removing ' ) why???


Code: 
strSQL = "SELECT IDeventask as îæää_àéøåò, ID_task AS îæää_îùéîä, name_task as ùí_îùéîä , duedate_task as úàøéê_çæåé, dodate_task as úàøéê_áéöåò, result_task as úåöàä ,okornot as _ú÷éï, statustask as ÷åã_ñèàèåñ, stat as ñèàèåñ ,  tasks_diary.machinenum as ùí_îëåðä, production_diary1.lognum as îæää_çéáåø, worker_pro AS òåáã, date_pro AS úàøéê_îùîøú  " _
& " FROM ((tasks_diary LEFT JOIN production_diary1 ON tasks_diary.lognum=production_diary1.lognum) LEFT JOIN tasks ON tasks_diary.ID_task=tasks.ID) LEFT JOIN status ON tasks_diary.statustask=status.ID" _
& " WHERE tasks_diary.[machinenum]='" & macs & "' AND [worker_pro]='" & works & "' AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"
2. the macs comes from listbox
in addition to this listbox, user can select checkbox to select all the machines in the listbox
so I thought to leave macs as string
and if the user select the checkbox, I pass by macs the string "IS NOT NULL"
there are more efficient ways to deal with it?

thanks
Ben
 
isladogs

isladogs

MVP / VIP
Local time
Today, 16:21
Joined
Jan 14, 2017
Messages
18,213
Ben

Can you give a couple of records of example data to explain your issue.
Obviously change any private data but keep the basic data structure the same

I'm not sure what you mean by "remember" in your post
 
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
I have some tables which long field - machinenum (machine numbers).
I created some recordsets for each sheet in the file I export to.
I have a multi-selected listbox with values which represent machine numbers,
the user select the machines , he wants to export records for.
Instead select in the listbox, user can sign in checkbox he wants all the machines.
I thought about "macs" variable...it's supposed to keep all the selected machine numbers, I thought to make it as string although machine numbers are long cause if user sign the checkbox I can make macs equal to "IS NOT NULL" and I will receive all the machines.

for every recordset I success use with ' and macs as string
except 1 recordset, which don't success accept it as string unless I remove '
I wonder why... cause this field is defined in the same manner in the other tables

the second question is how to deal with two options (select of all the machine , or select few machine) in the same sql query.

thanks
 
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
benjamin.weizmann said:
I have some tables which long field - machinenum (machine numbers).
I created some recordsets for each sheet in the file I export to.
I have a multi-selected listbox with values which represent machine numbers,
the user select the machines , he wants to export records for.
Instead select in the listbox, user can sign in checkbox he wants all the machines.
I thought about "macs" variable...it's supposed to keep all the selected machine numbers, I thought to make it as string although machine numbers are long cause if user sign the checkbox I can make macs equal to "IS NOT NULL" and I will receive all the machines.

for every recordset I success use with ' and macs as string
except 1 recordset, which don't success accept it as string unless I remove '
I wonder why... cause this field is defined in the same manner in the other tables

the second question is how to deal with two options (select of all the machine , or select few machine) in the same sql query.

thanks
Click to expand...

ok so it's not define in the same manner,

the machinenum field of the problematic table is define:

Code: 
SELECT machine.IDnum, machine.namemach FROM machine; union SELECT '999', 'genral' FROM machine;

while machinenum is define in all other tables as
Code: 
SELECT [machine].[IDnum], [machine].[namemach] FROM machine;

but what now?
 
isladogs

isladogs

MVP / VIP
Local time
Today, 16:21
Joined
Jan 14, 2017
Messages
18,213
Sorry Ben but your latest replies aren't making your issue much clearer than before.

Your two SQL statements aren't defining what datatype machinenum is.
I think its Number ...long integer but please clarify

I did ask for example data to clarify your post.

If you have an issue with the UNION query then check the following is true in each part of the UNION:
a) you have the same number of fields
b) the datatypes for each field are identical for the equivalent field

If either of the above isn't true, the UNION query will fail
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:21
Joined
May 7, 2009
Messages
19,230
add all selected items of listbox to a string
variable.
use Instr() to find if a machinenum is
in this list:
Code: 
Dim strSQL As String
Dim var As Variant
Dim strMacs As String
For Each var In Me.macs.ItemsSelected
    strMacs = strMacs & Me.macs.ItemData(var) & "\"
Next
strSQL = "SELECT IDeventask as îæää_àéøåò, ID_task AS îæää_îùéîä, name_task as ùí_îùéîä , duedate_task as úàøéê_çæåé, dodate_task as úàøéê_áéöåò, result_task as úåöàä ,okornot as _ú÷éï, statustask as ÷åã_ñèàèåñ, stat as ñèàèåñ ,  tasks_diary.machinenum as ùí_îëåðä, production_diary1.lognum as îæää_çéáåø, worker_pro AS òåáã, date_pro AS úàøéê_îùîøú  " _
& " FROM ((tasks_diary LEFT JOIN production_diary1 ON tasks_diary.lognum=production_diary1.lognum) LEFT JOIN tasks ON tasks_diary.ID_task=tasks.ID) LEFT JOIN status ON tasks_diary.statustask=status.ID" _
& " WHERE Instr('" & strMacs & "', strtasks_diary.[machinenum])>0 AND [worker_pro]='" & works & "' AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"
 
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
arnelgp said:
add all selected items of listbox to a string
variable.
use Instr() to find if a machinenum is
in this list:
Code: 
Dim strSQL As String
Dim var As Variant
Dim strMacs As String
For Each var In Me.macs.ItemsSelected
    strMacs = strMacs & Me.macs.ItemData(var) & "\"
Next
strSQL = "SELECT IDeventask as îæää_àéøåò, ID_task AS îæää_îùéîä, name_task as ùí_îùéîä , duedate_task as úàøéê_çæåé, dodate_task as úàøéê_áéöåò, result_task as úåöàä ,okornot as _ú÷éï, statustask as ÷åã_ñèàèåñ, stat as ñèàèåñ ,  tasks_diary.machinenum as ùí_îëåðä, production_diary1.lognum as îæää_çéáåø, worker_pro AS òåáã, date_pro AS úàøéê_îùîøú  " _
& " FROM ((tasks_diary LEFT JOIN production_diary1 ON tasks_diary.lognum=production_diary1.lognum) LEFT JOIN tasks ON tasks_diary.ID_task=tasks.ID) LEFT JOIN status ON tasks_diary.statustask=status.ID" _
& " WHERE Instr('" & strMacs & "', strtasks_diary.[machinenum])>0 AND [worker_pro]='" & works & "' AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"
Click to expand...

thanks! but it can be more then 100
is it still the right way>?
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:21
Joined
May 7, 2009
Messages
19,230
Here is the replacement.
Actually, we can go with the previous code, since String can accommodate 65,000 chars.
Code: 
Dim strSQL As String
Dim var As Variant
Dim strMacs As String
Dim strReplacement As String

If Me.macs.ListCount = Me.macs.ItemsSelected.Count Then
    'All are selected
    strReplacement = "tasks_diary.[machinenum]=tasks_diary.[machinenum]"
Else
    For Each var In Me.macs.ItemsSelected
        strMacs = strMacs & Me.macs.ItemData(var) & "\"
    Next
    strReplacement = "Instr('" & strMacs & "', tasks_diary.[machinenum])>0"
End If
strSQL = "SELECT IDeventask as îæää_àéøåò, ID_task AS îæää_îùéîä, name_task as ùí_îùéîä , duedate_task as úàøéê_çæåé, dodate_task as úàøéê_áéöåò, result_task as úåöàä ,okornot as _ú÷éï, statustask as ÷åã_ñèàèåñ, stat as ñèàèåñ ,  tasks_diary.machinenum as ùí_îëåðä, production_diary1.lognum as îæää_çéáåø, worker_pro AS òåáã, date_pro AS úàøéê_îùîøú  " _
& " FROM ((tasks_diary LEFT JOIN production_diary1 ON tasks_diary.lognum=production_diary1.lognum) LEFT JOIN tasks ON tasks_diary.ID_task=tasks.ID) LEFT JOIN status ON tasks_diary.statustask=status.ID" _
& " WHERE @p AND [worker_pro]='" & works & "' AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"

strSQL = Replace(strSQL, "@p", strReplacement)
 
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
arnelgp said:
Here is the replacement.
Actually, we can go with the previous code, since String can accommodate 65,000 chars.
Code: 
Dim strSQL As String
Dim var As Variant
Dim strMacs As String
Dim strReplacement As String

If Me.macs.ListCount = Me.macs.ItemsSelected.Count Then
    'All are selected
    strReplacement = "tasks_diary.[machinenum]=tasks_diary.[machinenum]"
Else
    For Each var In Me.macs.ItemsSelected
        strMacs = strMacs & Me.macs.ItemData(var) & "\"
    Next
    strReplacement = "Instr('" & strMacs & "', tasks_diary.[machinenum])>0"
End If
strSQL = "SELECT IDeventask as îæää_àéøåò, ID_task AS îæää_îùéîä, name_task as ùí_îùéîä , duedate_task as úàøéê_çæåé, dodate_task as úàøéê_áéöåò, result_task as úåöàä ,okornot as _ú÷éï, statustask as ÷åã_ñèàèåñ, stat as ñèàèåñ ,  tasks_diary.machinenum as ùí_îëåðä, production_diary1.lognum as îæää_çéáåø, worker_pro AS òåáã, date_pro AS úàøéê_îùîøú  " _
& " FROM ((tasks_diary LEFT JOIN production_diary1 ON tasks_diary.lognum=production_diary1.lognum) LEFT JOIN tasks ON tasks_diary.ID_task=tasks.ID) LEFT JOIN status ON tasks_diary.statustask=status.ID" _
& " WHERE @p AND [worker_pro]='" & works & "' AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"

strSQL = Replace(strSQL, "@p", strReplacement)
Click to expand...

I really wanna cry
I tried all my best but something wrong!
I'm sitting already 6 hours!! :((((

Code: 
Private Sub Command2070_Click()
Dim varItem As Variant
Dim clientmac As String
Dim clientwork As String
Dim lngRow As Long

If IsNull(Me.stard.Value) Or IsNull(Me.findate.Value) Then
    MsgBox "îìà úàøéëéí"
    Exit Sub
End If
If Me.allmach.Value = True Then
        For lngRow = 0 To machlist.ListCount - 1
            machlist.Selected(lngRow) = True
        
  If clientmac = "" Then
            clientmac = Me.machlist.Column(0, lngRow) & " "
        Else
            clientmac = clientmac & "OR [machinenum]=" & Me.machlist.Column(0, lngRow) & " "

        End If
   Next lngRow
Else
 
    For Each varItem In Me.machlist.ItemsSelected

        If clientmac = "" Then
            clientmac = Me.machlist.Column(0, varItem) & " "
        Else
            clientmac = clientmac & "OR [machinenum]=" & Me.machlist.Column(0, varItem) & " "

        End If

    Next varItem
End If
 
 
 

If Me.allworker.Value = True Then
  
  
          For lngRow = 0 To worklist.ListCount - 1
           worklist.Selected(lngRow) = True
        
  If clientwork = "" Then
            clientwork = Me.worklist.Column(0, lngRow) & " "
        Else
            clientwork = clientwork & "OR [machinenum]=" & Me.worklist.Column(0, lngRow) & " "

        End If
   Next lngRow
  
  
  
Else
    For Each varItem In Me.worklist.ItemsSelected
        If clientwork = "" Then
            clientwork = Me.worklist.Column(0, varItem) & " "
        Else
            clientwork = clientwork & "OR [worker_pro]=" & Me.worklist.Column(0, varItem) & " "
        End If
    Next varItem
End If
 

Call exportexcel(clientmac, clientwork, Me.stard.Value, Me.findate.Value)
End Sub
Code: 
 Sub exportexcel(macs As String, works As String, starts As Date, endds As Date)
Dim dbs As DAO.Database
Dim adresssave As String
Dim file_name As String

file_name = "ãåç îëáùéí " & Day(starts) & "." & Month(starts) & "." & Year(starts) & "-" & Day(endds) & "." & Month(endds) & "." & Year(endds)
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
 
 If IsNull(DLookup("[stringvalue]", "tech_data", "[name_parameter]='targetexport'")) = True Then
 MsgBox "ìà ðîöà ëúåáú ðúéá áîôøè äèëðé ùì äúåëðä, äúçáø ëîðäì åäâãø ðúéá"
 Exit Sub
 End If
 
 adresssave = DLookup("[stringvalue]", "tech_data", "[name_parameter]='targetexport'") & "\" & file_name & ".xlsx"
 
 Set dbs = CurrentDb


strSQL = "SELECT id_event as îñôø_àéøåò, ID_fault AS îæää_ú÷ìä, name_fault as ùí_ú÷ìä, date_start_fault as úçéìú_ú÷ìä, date_repair_fault as ñéåí_ú÷ìä, production_diary1.lognum as îæää_çéáåø,worker_pro AS òåáã, date_pro AS úàøéê_îùîøú , detail as ôøèéí ,[machinenum] as ùí_îëåðä ,[sub_qmfault] as ú÷ìú_àéëåú" _
& " FROM (fault_diary INNER JOIN  fault_list ON fault_diary.ID_fault=fault_list.ID) LEFT JOIN production_diary1 ON fault_diary.lognum=production_diary1.lognum" _
[COLOR=red]& " WHERE [machinenum]='" & macs & "' AND [worker_pro]='" & works & "'[/COLOR] AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"
 
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
............................................
all the time I get error "data type mismatch in criteria expression"
 
Last edited:
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
I don't know how and why
I did it before and it didn't success
now it success
I removed all the '
 
isladogs

isladogs

MVP / VIP
Local time
Today, 16:21
Joined
Jan 14, 2017
Messages
18,213
I expect that it worked because the datatype was a number so removing the text delimiters will have fixed it.

I was asking you for sample data so I could check exactly what you needed to alter
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:21
Joined
May 7, 2009
Messages
19,230
first you need to code the AfterUpdate
Event of the allmach and allworkder checkbox:

Code: 
Private Sub allmach_AfterUpdate()
Dim i As Long
For i = 0 to Me.machlist.ListCount-1
	Me.machlist.Selected(i)=allmach.Value
Next
End Sub

Private Sub allworker_AfterUpdate()
Dim i As Long
For i = 0 to Me.worklist.ListCount-1
	Me.worklist.Selected(i)=allworker.Value
Next
End Sub

next your Command2070 click event:
Code: 
Private Sub Command2070_Click()
Dim var As Variant
Dim strClientMac As String
Dim strClientWork As String
If Me.allmach Then
	strClientMac = "[machinenum]=[machinenum]"
Else
	For Each var In Me.machlist.ItemsSelected
		strClientMac = strClienMac & Me.machlist.ItemData(0, var) & "/"
	Next
	strClientMac = "Instr(" & """" & strClientMac & """" & ",[machinenum] & '')>0"
End If
If Me.allworker Then
	strClientWork = "[worker_pro]=[worker_pro]"
Else
	For Each var In Me.workList.ItemsSelected
		strClientWork = strClientWork & Me.worklist.ItemData(0,var) & "/"
	Next
	strClientWork = "Instr(" & """" & strClientWork & """" & ",[worker_pro] & '')>0"
End If
Call exportExcel(strClientMac, strClientWork, Me.stard.Value, Me.Findate.Value)
End Sub

Sub exportExcel(macs As String, works As String, starts As Date, endds As Date)
Dim dbs As DAO.Database
Dim adresssave As String
Dim file_name As String

"ãåç îëáùéí " & Day(starts) & "." & Month(starts) & "." & Year(starts) & "-" & Day(endds) & "." & Month(endds) & "." & Year(endds)
Dim qdfTemp As DAO.QueryDef
Dim strSQL As String, strQDF As String
 
 If IsNull(DLookup("[stringvalue]", "tech_data", "[name_parameter]='targetexport'")) = True Then
 MsgBox "ìà ðîöà ëúåáú ðúéá áîôøè äèëðé ùì äúåëðä, äúçáø ëîðäì åäâãø ðúéá"
 Exit Sub
 End If
 
 adresssave = DLookup("[stringvalue]", "tech_data", "[name_parameter]='targetexport'") & "\" & file_name & ".xlsx"
 
 Set dbs = CurrentDb


strSQL = "SELECT id_event as îñôø_àéøåò, ID_fault AS îæää_ú÷ìä, name_fault as ùí_ú÷ìä, date_start_fault as úçéìú_ú÷ìä, date_repair_fault as ñéåí_ú÷ìä, production_diary1.lognum as îæää_çéáåø,worker_pro AS òåáã, date_pro AS úàøéê_îùîøú , detail as ôøèéí ,[machinenum] as ùí_îëåðä ,[sub_qmfault] as ú÷ìú_àéëåú" _
& " FROM (fault_diary INNER JOIN  fault_list ON fault_diary.ID_fault=fault_list.ID) LEFT JOIN production_diary1 ON fault_diary.lognum=production_diary1.lognum" _
& " WHERE @1 AND @2 AND [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "# AND [date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#;"

strSQL = Replace(strSQL, "@1", macs)
strSQL = Replace(strSQL, "@2", works) 
strQDF = "_TempQuery_"
Set qdfTemp = dbs.CreateQueryDef(strQDF, strSQL)
 
Last edited:
B

benjamin.weizmann

Registered User.
Local time
Today, 08:21
Joined
Aug 30, 2016
Messages
78
ridders said:
I expect that it worked because the datatype was a number so removing the text delimiters will have fixed it.

I was asking you for sample data so I could check exactly what you needed to alter
Click to expand...

but now i'have new problem
it doesn't do what I ask in the criteria

for example:

it's the code

Code: 
strSQL = "SELECT id_event as îñôø_àéøåò, ID_fault AS îæää_ú÷ìä, name_fault as ùí_ú÷ìä, date_start_fault as úçéìú_ú÷ìä, date_repair_fault as ñéåí_ú÷ìä, production_diary1.lognum as îæää_çéáåø,worker_pro AS òåáã, date_pro AS úàøéê_îùîøú , detail as ôøèéí ,[machinenum] as ùí_îëåðä ,[sub_qmfault] as ú÷ìú_àéëåú" _
& " FROM (fault_diary INNER JOIN  fault_list ON fault_diary.ID_fault=fault_list.ID) LEFT JOIN production_diary1 ON fault_diary.lognum=production_diary1.lognum" _
& " WHERE [machinenum]=" & macs & " AND ([worker_pro]=" & works & ") AND ( [date_pro]>=#" & Format(starts, "yyyy/mm/dd hh:nn:ss") & "#) AND ([date_pro]<=#" & Format(endds, "yyyy/mm/dd hh:nn:ss") & "#);"


it's the query result as access object:


Code: 
SELECT fault_diary.id_event AS מספר_אירוע, fault_diary.ID_fault AS מזהה_תקלה, fault_list.name_fault AS שם_תקלה, fault_diary.date_start_fault AS תחילת_תקלה, fault_diary.date_repair_fault AS סיום_תקלה, production_diary1.lognum AS מזהה_חיבור, production_diary1.worker_pro AS עובד, production_diary1.date_pro AS תאריך_משמרת, fault_diary.detail AS פרטים, fault_diary.[machinenum] AS שם_מכונה, fault_diary.[sub_qmfault] AS תקלת_איכות
FROM (fault_diary INNER JOIN fault_list ON fault_diary.ID_fault = fault_list.ID) LEFT JOIN production_diary1 ON fault_diary.lognum = production_diary1.lognum
WHERE (((fault_diary.[machinenum])=1 Or (fault_diary.[machinenum])=2 Or (fault_diary.[machinenum])=3 Or (fault_diary.[machinenum])=4 Or (fault_diary.[machinenum])=5)) OR (((fault_diary.[machinenum])=6) AND ((production_diary1.[worker_pro])=33) AND ((production_diary1.[date_pro])>=#1/1/2017# And (production_diary1.[date_pro])<=#12/31/2017#));


but it shows for every worker
not just 33 like I asked.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom