Hi,
here are a few functions that might make live a bit easier when constructing
such strings, just copy paste in a module
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' SQL values, variables transformed in correct SQL values to be used in SQL
statements
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
' SQLvalueDate : returns a string with the date formatted properly for
SQL purposes
' SQLvalueTime : returns a string with the time formatted properly for
SQL purposes
' SQLvalueDateTime : returns a string with the date and time formatted
properly for SQL purposes
' SQLvalue : returns a string with a numeric or stringvalue
formatted properly for SQL
' purposes. Numeric values returned as string, string
values returned enclosed in quotes
' WithQuotes : returns a string enclosed in double quotes ( " )
Public Function SQLvalueDate(value As Date) As String
Const JetDateFmt = "\#mm\/dd\/yyyy\#;;;\N\u\l\l"
SQLvalueDate = Format$(value, JetDateFmt)
End Function
Public Function SQLvalueTime(value As Date) As String
Const JetTimeFmt = "\#hh\:nn\:ss\#;;;\N\u\l\l"
SQLvalueTime = Format$(value, JetTimeFmt)
End Function
Public Function SQLvalueDateTime(value As Date) As String
Const JetDateTimeFmt = "\#mm\/dd\/yyyy hh\:nn\:ss\#;;;\N\u\l\l"
SQLvalueDateTime = Format$(value, JetDateTimeFmt)
End Function
Public Function SQLvalue(value As Variant) As String
If IsNumeric(value) Then
SQLvalue = value
Else
SQLvalue = WithQuotes(value)
End If
End Function
Public Function WithQuotes(value As Variant) As String
WithQuotes = Chr(34) & value & Chr(34)
End Function
hope it helps to solve your issue.
regards,
Tieske
On Nov 19, 9:19 pm, Brian <
[email protected]> wrote:
- Hide quoted text -
- Show quoted text -
You can dimension your variables as Variants to allow them to accept
nulls
Alternatively, replace Null with an empty string using the NZ function:
strSpecialCD = NZ(DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*"""),"")
This replaces the DLookup value with an empty string if the DLookup value
is
null - before assigning it to the variable, so the variable can be a
string
type.
:
Hi,
I create a simple lookup and it runs good. I only get error "Null"
from the table.
Dim strProject As String
Dim strSpecialCD As String
strDivMgr = DLookup("[Report]", "tblDivname", "[Report] Like ""*"
& DivMgr!Report.Value & "*""")
strProject = DLookup("[Project]", "tbl_List", "[Report] like ""*"
& DivMgr!Report.Value & "*""")
strSpecialCD = DLookup("[SpecialCD]", "tbl_List", "[Report] like
""*" & DivMgr!Report.Value & "*""")
It works fine when I use Project and List with no problem but using
SpecialCD. I kept get error because of Null. Is there a way to
take either the value or null when using the Dlookup (strSpecialCD)
tbl_List
SpecialCD Project List
Living Room Carpet Group A
<null> Bedroom Group C
<null> Kitchen Goup D
Garage Car Group E
Your help would be much appreciated.
Thanks- Hide quoted text -
- Show quoted text -
Thank you very much. It works great but I'm having a trouble with
single quoate. I tried to use like CH(34) but kept giving error
from "Where (tblExport.Report)=" & strDivName" All others are ok
exept ie: Bob's DVD
strDivName = """" & DivMgr!Report.Value & """"
DoCmd.RunSQL "SELECT tblExport.Project, tblExport.Report,
tblExport.Query, tblExport.Worksheet INTO tbl_Reports FROM tblExport
WHERE (tblExport.Report)=" & strDivName
Your help much appreciated.
Thanks- Hide quoted text -
- Show quoted text -
