Access to Excel check box conversion
 

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD

Access to Excel check box conversion
 

Try this
Sub changeValues()
Const WHATCOLUMN As String = "A"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(WHATCOLUMN & Rows.Count).End(xlUp).Row
For iStartingRow = 1 To iLastRow
Select Case Range(WHATCOLUMN & iStartingRow).Value
Case Is = -1
Range(WHATCOLUMN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(WHATCOLUMN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD

Access to Excel check box conversion
 

Not working. This code goes in the Excel workbook, right? Do I need to
change anything to customize it to my needs?

--
GD


"Mike" wrote:

Try this
Sub changeValues()
Const WHATCOLUMN As String = "A"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(WHATCOLUMN & Rows.Count).End(xlUp).Row
For iStartingRow = 1 To iLastRow
Select Case Range(WHATCOLUMN & iStartingRow).Value
Case Is = -1
Range(WHATCOLUMN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(WHATCOLUMN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD

Access to Excel check box conversion
 

What column has the -1 and 0 and what row does your data start with??

"GD" wrote:

Not working. This code goes in the Excel workbook, right? Do I need to
change anything to customize it to my needs?

--
GD


"Mike" wrote:

Try this
Sub changeValues()
Const WHATCOLUMN As String = "A"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(WHATCOLUMN & Rows.Count).End(xlUp).Row
For iStartingRow = 1 To iLastRow
Select Case Range(WHATCOLUMN & iStartingRow).Value
Case Is = -1
Range(WHATCOLUMN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(WHATCOLUMN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD

Access to Excel check box conversion
 

Columns K-N, from row 2 to the bottom of the sheet (the size of the
spreadsheet will vary, depending on circumstances). Thanks!
--
GD


"Mike" wrote:

What column has the -1 and 0 and what row does your data start with??

"GD" wrote:

Not working. This code goes in the Excel workbook, right? Do I need to
change anything to customize it to my needs?

--
GD


"Mike" wrote:

Try this
Sub changeValues()
Const WHATCOLUMN As String = "A"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(WHATCOLUMN & Rows.Count).End(xlUp).Row
For iStartingRow = 1 To iLastRow
Select Case Range(WHATCOLUMN & iStartingRow).Value
Case Is = -1
Range(WHATCOLUMN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(WHATCOLUMN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD

Access to Excel check box conversion
 

Try this
Sub changeValues()
Const COLUMNK As String = "K"
Const COLUMNL As String = "L"
Const COLUMNM As String = "M"
Const COLUMNN As String = "N"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(COLUMNK & Rows.Count).End(xlUp).Row
For iStartingRow = 2 To iLastRow
Select Case Range(COLUMNK & iStartingRow).Value
Case Is = -1
Range(COLUMNK & iStartingRow).Value = "TRUE"
Case Is = 0
Range(COLUMNK & iStartingRow).Value = "FALSE"
End Select
Select Case Range(COLUMNL & iStartingRow).Value
Case Is = -1
Range(COLUMNL & iStartingRow).Value = "TRUE"
Case Is = 0
Range(COLUMNL & iStartingRow).Value = "FALSE"
End Select
Select Case Range(COLUMNM & iStartingRow).Value
Case Is = -1
Range(COLUMNM & iStartingRow).Value = "TRUE"
Case Is = 0
Range(COLUMNM & iStartingRow).Value = "FALSE"
End Select
Select Case Range(COLUMNN & iStartingRow).Value
Case Is = -1
Range(COLUMNN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(COLUMNN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Columns K-N, from row 2 to the bottom of the sheet (the size of the
spreadsheet will vary, depending on circumstances). Thanks!
--
GD


"Mike" wrote:

What column has the -1 and 0 and what row does your data start with??

"GD" wrote:

Not working. This code goes in the Excel workbook, right? Do I need to
change anything to customize it to my needs?

--
GD


"Mike" wrote:

Try this
Sub changeValues()
Const WHATCOLUMN As String = "A"
Dim iStartingRow As Long
Dim iLastRow As Long

iLastRow = Range(WHATCOLUMN & Rows.Count).End(xlUp).Row
For iStartingRow = 1 To iLastRow
Select Case Range(WHATCOLUMN & iStartingRow).Value
Case Is = -1
Range(WHATCOLUMN & iStartingRow).Value = "TRUE"
Case Is = 0
Range(WHATCOLUMN & iStartingRow).Value = "FALSE"
End Select
Next
End Sub

"GD" wrote:

Not sure if this is an Excel or Access question, but here goes:

Previously, when I performed a copy and paste from an Access query result to
an Excel spreadsheet, the check box values would convert from -1 to Yes, and
0 to No. For some reason, they no longer convert to anything, and are
transferred as -1 and 0.

Can anyone tell me how this happened, and how I can get back to getting the
values to convert to Yes and No??

Thanks!!
--
GD