Search results

Joseph is correct, June, a Variant variable initialises to Empty - what you see is as a result of datatype coercion. However, the point is that the function StringFormatSQL() handles NULL's passed as a parameter and correctly converts to the the word 'NULL' in the resulting output SQL string...

No, a variant can be whatever is put in to it. I *think* the StringFormatSQL() function will be able to determine what the passed datatype is. But it has the advantage that you can also put NULL in to it (ie when your textbox is empty/Null). The only problem might be if your currency fields in...

OK, but still I advise to use NULL instead of empty string! Try the following: Private Sub MatchTransfer() Dim strSql As String Dim AID As Long Dim NID As Long Dim CDt As Date Dim Nm As Variant Dim Mmo As String Dim TrnID As Long Dim TPay As Variant Dim TRec As Variant Dim TMmo As String Dim...

What happens if you change: Nm = Nz(Me.Parent.Num, "") to simply: Nm = Me.Parent.Num ? *** And also change the declaration of the variable: Dim Nm As String to: Dim Nm As Variant

You probably can. Which function are you currently using: StringFormatSQL() or fnAnySQL() ? Post your current VBA and it's probably only a small adjustment.

In addition to Dave ( @gemma-the-husky ), beware of inserting empty strings in to text fields. Unless an empty string has meaning, then it would be better to use NULL and handle those appropriately. Otherwise you can end up with weird results when trying to filter for 'empty' fields and you're...

Ah OK, good! I thought you were saying that the editor was formatting it like the left side, but you wanted it to remain like the right side! 🤪 I'm afraid I don't have anything useful to offer as a solution, except keep a copy of your queries properly formatted in a separate text file using...

You actually *want* to see the query formatted as in the example on the right?

I'm sure it could be used in code similar to yours though, but without having to loop all the module lines. However, to me, the REGEXP expression to be able to find blocks of code ignoring line continuation character as the end of what is being searched for is more complex than I can dream up...

3. Use BuildCriteria() ?

Thanks for your help. :rolleyes:

Yes - you are creating an SQL statement, so you need to explicitly put 'NULL' in there. Setting a field to the same value it is already is not a problem

Since it's so easy, what would the REGEXP expression be?

To help others who might have a similar requirement, please post the final SQL that you used.

Try adding NULL to the SQL when you want Null: strSql = "INSERT INTO tblTransactions" & _ " ([fAccountID]" & _ ", [fNameID]" & _ ", [CkDate]" & _ ", [Num])" & _ " VALUES" & _ " (" & Nz(Me.cboAccount.Value, "NULL") & _ ", " &...

Presumably via normalised tables: Shows -> Seasons -> Episodes

That's why I used the constant (SPACER) to calculate the tab widths without requiring the left/right arrows in the code I used.

PS. Are you using code to hide the ribbon?

Perhaps it didn't manifest under Win XP? I'm using Win 10

Interesting - I wonder what the difference is.