David David - 3 years ago 194
Vb.net Question

SQL WHERE BETWEEN not selecting all data

I've written an export (Access to Excel) function in an application that works in ranges.

The user has 4

: A-F, G-M, N-R and S-Z.

Let's say the user has selected
, which will load all customers into the grid where the
field starts with an A, B, C, D, E or F.

The code to load in the data is as follows:

Dim strFields As String = "[Customer_Addresses].[Cust_Code], [Customers].[Customer_Name], [Customer_Addresses].[Contact_Code], [Customer_Addresses].[Contact_Name], " & _
"[Customer_Addresses].[Contact_Type], [Customer_Addresses].[Add1], [Customer_Addresses].[Add2], [Customer_Addresses].[Add3], [Customer_Addresses].[Add4], " & _
"[Customer_Addresses].[Add5], [Customer_Addresses].[Postcode], [Customer_Addresses].[Country], [Customer_Addresses].[Telephone], [Customer_Addresses].[Fax], " & _
"[Customer_Addresses].[Email], [Customer_Addresses].[Mobile_Phone], [Customers].[Customer_Category], [Customers].[Average_Payment_Terms], " & _
"[Customers].[Notes], [Customers].[salesRep], [Customers].[hoEmail], [Customers].[webpage] FROM Customers " & _
"INNER JOIN Customer_Addresses ON [Customers].[Customer_Code] =[Customer_Addresses].[Cust_Code]"

If rbtnAF.Checked = True Then
sql = "SELECT " & strFields & " WHERE [Customer_Addresses].[Cust_Code] BETWEEN " & _
"'A*' AND 'F*' ORDER BY [Customer_Addresses].[Cust_Code]"

Dim da As New OleDbDataAdapter(sql, con)

Dim ds As New DataSet
Dim dt As New System.Data.DataTable

dt = ds.Tables(0).Copy()

ugExport.DataSource = Nothing
ugExport.DataSource = dt

This was, I thought, working fine, I was able to load the correct ranges into the grid and export them as I wanted.
However, the user has come back to me and said it's not loading all customers.

I thought this was a bit weird, so I loaded up their database and tested it for myself. In the DB, there are 4 customers who fit into the range of A-F, as you can see in this image.

enter image description here

However, when I then view the customer range A-F in the export list, there are only 2 customers displayed.

enter image description here

It's also worth noting, in the Customer List screen there is a
to allow the user to search by customer code - When I type in just a single F, all 4 customers are displayed as expected.

What on Earth is going on to only display 2 of the results in the Export List, despite there being 4 records that fit the criteria?

Answer Source

Checking the between syntax you will find that it treats * as a literal character see here https://support.office.com/en-us/article/Between-And-Operator-a435878d-63f7-4825-8c31-999432ae8223

You can use

Like "[A-F]*"

Instead though.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download