SQL formatting

[Josef P.]

Missing Option Explicit?

strTWhere = ("[SOP Nu ....
...
If DCount("*", "[Main TBL]", strWhere) > 0 Then
...

strTWhere vs. strWhere

 

[theDBguy]

Possibly

You can send me an email, if you want to set something up.

 

[Design by Sue]

Something is off here...

Thanks - that has to be part of it! I am not on that computer now - will be getting back to this later.

 

[Gasman]

That's the problem - the debug/print is blank

You need

Debug.Print strTWhere

Use use Debug.Print <YourVariable> AFTER you set anything, not before.

You have set strTWhere to some text so it cannot possibly be blank?

 

[Design by Sue]

You can send me an email, if you want to set something up.

Yes possibly on Monday? I am away til then.

 

[Design by Sue]

I am back to my original issues of data mismatch and I know it is because I have the strWhere statement incorrect but I don't know how to correct it. I am away until Monday and will be back at this again then. Have a great weekend everyone

 

[Gasman]

I am back to my original issues of data mismatch and I know it is because I have the strWhere statement incorrect but I don't know how to correct it. I am away until Monday and will be back at this again then. Have a great weekend everyone

You have been shown an easier method, where type mismatch is hard to accomplish?
@MajP has a function to build the SQL correctly.

Post in thread 'Insert Into Syntax issue'

Apr 27, 2021

FYI,
I use a CSQL function for all of my values. Saves a lot of time and gets it correct. If you are referencing a variant datattype (anything coming from a control) no need to specify the datatype. It will know if it is a string, numeric or date.

Public Enum SQL_DataType
  sdt_boundfield = -1
  sdt_UseSubType = 0
  sdt_text = 1
  sdt_Numeric = 2
  sdt_date = 3
  sdt_Boolean = 4
  sdt_Null = 5
End Enum
'**************************************************************************************************************
'----------------------------------------Convert To Delimited SQL...

• MajP

If you still insist on trying to build it yourself?

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.

 

[Design by Sue]

You can send me an email, if you want to set something up.

to theDBguy - I just sent you an email Please reply that if you did not get it - or let me know it did not come through to you. THANKS!

 

[Design by Sue]

Super big thanks to everyone Especially gasman for the explanation of how to set up strings with the correct notation (not sure of the correct wording) After what I call "poking it with a stick" I was able to find the correct sql stateiment. And also to theDBguy for our email exchanges willingness to connect via the web. So happy I can continue the fight!

 

[theDBguy]

Super big thanks to everyone Especially gasman for the explanation of how to set up strings with the correct notation (not sure of the correct wording) After what I call "poking it with a stick" I was able to find the correct sql stateiment. And also to theDBguy for our email exchanges willingness to connect via the web. So happy I can continue the fight!

Glad to hear you got it sorted out. Good luck with your project.