cheekybuddha
AWF VIP
- Local time
- Today, 21:25
- Joined
- Jul 21, 2014
- Messages
- 2,519
I did that and got the original problem to work - thank you - this is a new problem and the criteria is in a string variable isn't it?Follow @Gasman 's eternal advice:
Put your criteria into a string variable and examine it before passing to the DCount().
(Ah I see Pat has suggested the same in Post #7)
Well if you debug.printed it that would show?, and even if you cannot see it, you could paste back here and someone else could spot it for you.I think this is closer - fixed the formatting to match date and double (I think) but still there is s typo
strSQL = "Update [Main TBL] Set [DateCompleted]= #" & (strDateCompleted) & "# And TimeCompleted = " & (Me.TimeCompleted) & “ Where [Employee Number] = '" & strEmpNumber & " And [SOP Number] = '" & (strNumber) & " And SOPVersion = '" & strVersion & ""
strSQL
variable add a Debug.Print strSQL
line.strSQL
will be output in the Immediate Window (Ctrl + G) Const SQL_INSERT As String = _
"Insert Into tblFileRequestData " & _
"(File_Number, File_Name, File_Title, File_Requested_By, File_Requested_Date, File_Data_Entry_Date, File_Status) " & _
"Values " & _
"( p0, p1, p2, p3, p4, p5, p6);"
With CurrentDb.CreateQueryDef("", SQL_INSERT)
.Parameters(0) = flnum
.Parameters(1) = flnam
.Parameters(2) = fltitle
.Parameters(3) = usern
.Parameters(4) = flreqdate
.Parameters(5) =...
Yes I know I have gotten this totally confusing. I am going back to my previous code that I thought I had working. I should have put them in separate posts to avoid confusion on the board, And yes I am totally messed up regarding the data types and how to code them. I have also tried to set this up in a query and still get no results. I just don't understand this any more. Appreciate your trying to help. Is it the data types that is causing the code not to see the table info?You are really confused regarding how to use the various data types.
Delimiters:
* Strings are ALWAYS delimited. Use either single or double quotes.
* Dates formatted as strings are always delimited. Use the #
Numbers are NEVER delimited.
* When you reference form controls in a saved querydef (not from embedded SQL in your VBA), you don't need to delimit anything since Access knows enough to do it for you. When you create a query using VBA, the query itself is a string and strings cannot hold objects so all the fields referenced are strings and therefore must be properly delimited.
Save yourself some code and directly reference form fields rather than first moving them to intermediate variables. The only reason to take the intermediate step is if you are going to reference the form controls multiple times each. Referencing a form control is more "expensive" than referencing a locally defined variable. So copying form controls you are only going to reference once wastes time but copying form controls to variables saves time if they will be referenced multiple times, such is in a loop for example.
And finally, make sure you have the fields correctly defined in the tables. You keep waffling in the code you display but we have no idea what their real data types are.
I am trying to to that - really - but I just am not understanding how to write the codePlease, Please read what I have written and act on it.
1. Fix the data types in the tables.
2. Use the proper data type in your variables
3. Use the proper delimiters
4. Format dates correctly when you are using them as strings as you are when you build SQL strings using VBA
5. Build your strings into variables so they are easy to examine once built.
String functions work on strings. Numbers and Dates are not strings and would cause a type error. Remove the code where it is not needed. The function was probably created to handle names like O'Brian which have embedded quotes. This function doubles the single quote so it will not be interpreted as the start or end of a string.
Function ESC(str) As String
ESC = Replace(str, "'", "''")
End Function
Sub foo()
Dim str
str = CDate("01/01/2001")
MsgBox ESC(str)
End Sub
That's the problem - the debug/print is blankSue, you need to debug.print something, in your case strTWhere
Thanks!Not to get too picky but technically, since the ESC programmer did not write:
Function ESC(str as String) as String
... that means the passed in value need not necessarily be a string. (although I agree it would seem silly to pass nonstring values into it)..
For example, this actually works and does not err:
Code:Function ESC(str) As String ESC = Replace(str, "'", "''") End Function Sub foo() Dim str str = CDate("01/01/2001") MsgBox ESC(str) End Sub
Would you be open to a screen sharing session?At this point I am so lost - I just don't know what to do.
Possibly - I have made some progress in that I have the correct information displaying when I mouse over the codes in this sectionWould you be open to a screen sharing session?