how to add criteria using strwhere

S

sspreyer

Registered User.
Local time
Today, 11:20
Joined
Nov 18, 2013
Messages
251
how to add another strwhere criteria

hi
all

I have some code that exports data to excel overwrites old data on the same sheet every time call "exspose Data" but I now need to add another string to only export current year data

how it works it creates a temp query then exports to excel and it then delete's it
my vba knowledge is very little :(

here's my code it works but don't filter Dateyear to current year show all years :(

bold in red Text is the new lines I have added

Code: 
 Dim dbs As DAO.Database
Dim qryDef As DAO.QueryDef
Dim strWhere As String
Dim lngLen As Long
Set dbs = CurrentDb
Dim strSQL As String
    strSQL = "SELECT tblemployee.Id, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname, [COLOR=red][B]Year([Dateexposure]) AS DateYear[/B][/COLOR]  " & _
         " FROM tblExposure " & _
         " INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID "
    
    If [Forms]![Frmemployee]![CboMoveTo] <> "" Then
        strWhere = strWhere & "([tblemployee.ID] = " & [Forms]![Frmemployee]![CboMoveTo] & ") AND "
    End If
 
lngLen = Len(strWhere) - 5
 
        DoCmd.Hourglass True
        
 
        If lngLen <= 0 Then
            strSQL = strSQL
            Set qryDef = dbs.CreateQueryDef("Exposure Data", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\excel test.xlsm", "Exposure Data"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Data"
        Else
            strWhere = Left$(strWhere, lngLen)
 
            strSQL = strSQL & " WHERE " & strWhere
           Set qryDef = dbs.CreateQueryDef("Exposure Data", strSQL)
            qryDef.Close
            Set qryDef = Nothing
            DoEvents
            SaveQueriesToExcel "C:\Users\Dell\Documents\excel test.xlsm", "Exposure Data"
            DoEvents
            DoCmd.DeleteObject acQuery, "Exposure Data"
        End If
       
 
        DoCmd.Hourglass False
 
    
        DoCmd.CancelEvent
and some how I need to add this as my where str :confused: I think

Code: 
((Year([Dateexposure]))=Year(Now())));"
unsure how to add it

thanks in advance

shane
 
Last edited:
Just to be sure...

You don't want the option to select the year... ever?
 
hi
Gina
good idea!!! so if make a form call frmexportexcel and combo
box with years called cboexportyear

so what would the where string be :confused:

thanks
shane
 
You wouldn't need the line you could just include something along the lines of...

Code: 
    If [Forms]![Frmemployee]![CboMoveTo] <> "" Then
        strWhere = strWhere & "([tblemployee.ID] = " & [Forms]![Frmemployee]![CboMoveTo] & ") AND "
    End If
...but changing the names to match you Field/Control names and either add it to this exisitng Form OR make a new Form and copy all the code and then changes the Field/Control names.
 
right tblExposure.Dateexposure holds the date for each record
Code: 
im dbs As DAO.Database
Dim qryDef As DAO.QueryDef
Dim strWhere As String
Dim lngLen As Long
Set dbs = CurrentDb
Dim strSQL As String
strSQL = "SELECT tblemployee.Id, tblExposure.Dateexposure, [Valuef/cm]*[Durationofexposure] AS [Total Ex], tblemployee.firstname, tblemployee.lastname " & _
" FROM tblExposure " & _
" INNER JOIN tblemployee ON tblExposure.ID = tblemployee.ID "
If [Forms]![Frmemployee]![CboMoveTo] <> "" Then
strWhere = strWhere & "([tblemployee.ID] = " & [Forms]![Frmemployee]![CboMoveTo] & ") AND "
End If
[COLOR=red][B]If [Forms]![frmexportexcel]![cboexportyear] <> "" Then
strWhere = strWhere & "([tblExposure.Dateexposure] = " & [Forms]![frmexportexcel]![cboexportyear] & ") AND "
End If[/B][/COLOR]

lngLen = Len(strWhere) - 5

DoCmd.Hourglass True


If lngLen <= 0 Then
strSQL = strSQL
Set qryDef = dbs.CreateQueryDef("Exposure Data", strSQL)
qryDef.Close
Set qryDef = Nothing
DoEvents
SaveQueriesToExcel "C:\Users\Dell\Documents\excel test.xlsm", "Exposure Data"
DoEvents
DoCmd.DeleteObject acQuery, "Exposure Data"
Else
strWhere = Left$(strWhere, lngLen)

strSQL = strSQL & " WHERE " & strWhere
Set qryDef = dbs.CreateQueryDef("Exposure Data", strSQL)
qryDef.Close
Set qryDef = Nothing
DoEvents
SaveQueriesToExcel "C:\Users\Dell\Documents\excel test.xlsm", "Exposure Data"
DoEvents
DoCmd.DeleteObject acQuery, "Exposure Data"
End If


DoCmd.Hourglass False


DoCmd.CancelEvent
End Sub
but I get error run time error 2465
Microsoft access can find field 'cboexportyear' reference in your expression

all so being Dateexposure is date field i.e. like 20/07/2014 I only want it to check for year e.g 2014 one if this would cause expression probem

thanks

again

shane
 
GinaWhipp said:
Oh, right, try...

Code: 
Year([Dateexposure])
Click to expand...

sorry gina im confused where to put this now :banghead::confused:
Code: 
 [COLOR=#ff0000][B]strWhere = strWhere & "([tblExposure.Dateexposure] = " & [Forms]![frmexportexcel]![cboexportyear] & ") AND "[/B][/COLOR]

thanks

shane
 
Here you go...

Code: 
strWhere = strWhere & "(Year([Dateexposure]) = " & [Forms]![frmexportexcel]![cboexportyear] & ") AND "
 
thanks Gina

still get Microsoft access can find field 'cboexportyear' reference in your expression

and its highlighting
this row

Code: 
       If [Forms]![frmexportexcel]![cboexportyear] <> "" Then
thanks

shane
 
Well, double check the name of the field on the Form, sounds like that is not the correct name. Maybe a typo?
 
right I change the combo name to combo3 for temporally and it work's but didn't export any records it was blank but there is definably record's in that criteria:confused::confused:
I set my combo box to =2014

Code: 
  If [Forms]![frmexportexcel]![Combo3] <> "" Then
        strWhere = strWhere & "(Year([Dateexposure]) = " & [Forms]![frmexportexcel]![Combo3] & ") AND "
    End If
 
Is this a TEXT field? Try...

Code: 
If [Forms]![frmexportexcel]![Combo3] <> "" Then
strWhere = strWhere & "(Year([Dateexposure]) = """ & [Forms]![frmexportexcel]![Combo3] & """) AND "
End If
 
right I have found what was wrong I was running my combo box from a table but when I was selecting a year it must of been taking its record ID not the Year Field value so I change it to field list box and boom! it worked!!!

Thanks Gina YET AGAIN!!!! YOU LEGEND!!!!:D:)

shane
 
You must log in or register to reply here.

Similar threads

D
How to add a title to the search results in Excel 2016 using VBA
Replies
6
Views
957
Isaac
Isaac
D
Join Expression Not Supported
Replies
12
Views
3,260
Drand
D
C
Access-Excel. How to edit an excel file from Access using SQL
Replies
5
Views
2,139
NauticalGent
NauticalGent
J
Exporting specific fields from from results using SQL query is showing all fields.
Replies
1
Views
1,201
pbaldy
pbaldy
M
Export to excel using nested query as recordset?
Replies
3
Views
951
pbaldy
pbaldy

Users who are viewing this thread

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