Define between Number & Alphabet Acc97

  • Thread starter Thread starter noodnutt
  • Start date Start date
N

noodnutt

G'day guy's

Here's the deal:

tblTitle:-
txtTitle, txtGenre, txtAlpha

for reporting purposes I am using txtalpha to filter the txtTitle A-Z
although it was a little trickier with numbers as it will display a
different numeral category for each title that has a different title eg 12
Monkeys or 28 Days Later etc to get around this I have been inputing each
Alpha using # to represent titles that start with a numeral.

I was thinking that it could be done in the After_Update of txtTitle,
something like this.

Private Sub Title_AfterUpdate()

If Me.Title.{firstcharacter} = <Letter> Then
Me.Alpha = Left([Title], 1)
Else
If Me.Title.{firstcharacter} = <Number> Then
Me.Alpha = "#"
EndIf
End Sub

Now I am fully aware that the sytax structure isn't %100 accurate, but I
think you will get the drift

TIA

Mark
 
noodnutt said:
G'day guy's

Here's the deal:

tblTitle:-
txtTitle, txtGenre, txtAlpha

for reporting purposes I am using txtalpha to filter the txtTitle A-Z
although it was a little trickier with numbers as it will display a
different numeral category for each title that has a different title eg 12
Monkeys or 28 Days Later etc to get around this I have been inputing each
Alpha using # to represent titles that start with a numeral.

I was thinking that it could be done in the After_Update of txtTitle,
something like this.

Private Sub Title_AfterUpdate()

If Me.Title.{firstcharacter} = <Letter> Then
Me.Alpha = Left([Title], 1)
Else
If Me.Title.{firstcharacter} = <Number> Then
Me.Alpha = "#"
EndIf
End Sub

Now I am fully aware that the sytax structure isn't %100 accurate, but I
think you will get the drift

TIA

Mark
Click to expand...

You Alpha field is unnecessary. You can create queries which sort as
required when you actually perform an output e.g.

SELECT txtTitle, txtGenre FROM tblTitle ORDER BY
IsNumeric(Left(txtTitle,1)), txtTitle
 
G'day Brian

Appreciate your input, although it still leaves me with the problem of
multiple numeric categories.

I display all titles beginning with a numeric as "#", this way they are
grouped together when the report prints.

Reg's
Mark.

Brian said:
noodnutt said:
G'day guy's

Here's the deal:

tblTitle:-
txtTitle, txtGenre, txtAlpha

for reporting purposes I am using txtalpha to filter the txtTitle A-Z
although it was a little trickier with numbers as it will display a
different numeral category for each title that has a different title eg 12
Monkeys or 28 Days Later etc to get around this I have been inputing each
Alpha using # to represent titles that start with a numeral.

I was thinking that it could be done in the After_Update of txtTitle,
something like this.

Private Sub Title_AfterUpdate()

If Me.Title.{firstcharacter} = <Letter> Then
Me.Alpha = Left([Title], 1)
Else
If Me.Title.{firstcharacter} = <Number> Then
Me.Alpha = "#"
EndIf
End Sub

Now I am fully aware that the sytax structure isn't %100 accurate, but I
think you will get the drift

TIA

Mark
Click to expand...

You Alpha field is unnecessary. You can create queries which sort as
required when you actually perform an output e.g.

SELECT txtTitle, txtGenre FROM tblTitle ORDER BY
IsNumeric(Left(txtTitle,1)), txtTitle
Click to expand...
 
noodnutt said:
G'day Brian

Appreciate your input, although it still leaves me with the problem of
multiple numeric categories.

I display all titles beginning with a numeric as "#", this way they are
grouped together when the report prints.

Reg's
Mark.
Click to expand...

The expresison:

IsNumeric(Left(txtTitle,1))

has the value True (that is, -1) when txtTitle begins with a number, and
False (0) otherwise. You can do what you like with this expression. In my
previous answer, I showed how you can sort on it to get the
numerically-initialled titles to appear first. If you add the expression to
the field list in the query, you can then group on it in a report if you
want:

SELECT txtTitle, txtGenre, IsNumeric(Left(txtTitle,1)) AS
StartsWithNumber FROM tblTitle ORDER BY IsNumeric(Left(txtTitle,1)),
txtTitle

There really is no need to clutter up your database with information that
you can easily derive whenever you need it.
 
Back
Top