Entering data into a field

B

Bob Vance

I have added a new field to my table now I have to put the same date in
every record is there and easier way other than pasting in each record!
 
D

Dirk Goldgar

Bob Vance said:
I have added a new field to my table now I have to put the same date in
every record is there and easier way other than pasting in each record!
Click to expand...


Run an update query. You can build an update query in the query design
grid -- you have to change the query type from the default -- or you can
build it in SQL view, where it would look vaguely like this:

UPDATE YourTable SET YourField = #9/15/2008#;

You only need to run the query once to update all existing records, and you
don't have to save it.
 
B

Bob Vance

This Function Dues code is not working properly, the Credits are not coming
off from the OLDEST total owing
So In my TblAccountStatus I created another field BilldateS defaulted to 1
sep 1970 and made in Invisible, then changed my query below from
[tblAccountStatus.BillDate] to [tblAccountStatus.BillDateS] so now my code
always starts it function from 3 Months old and works down
BELIEVE it or not it works :) :) :)
--------------------------------------------------------
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName, tblInvoice.InvoiceDate AS
OnDate,iif(tblInvoice.ClientInvoice=true,tblInvoice.OwnerName,funGetHorse(tblInvoice.InvoiceID)
& " " & Format(tblInvoice.OwnerPercent,"0.0%")) AS Description,
tblInvoice.OwnerPercentAmount AS AmountSummary, tblInvoice.InvoiceID,
tblInvoice.InvoiceNo,0 AS Flag
FROM tblInvoice
WHERE tblInvoice.OwnerID=Forms!frmBillStatement!cbOwnerName
UNION SELECT tblAccountStatus.OwnerID, NULL, tblAccountStatus.BillDateS AS
OnDate, tblAccountStatus.ModeOfPayment AS
Description,(tblAccountStatus.PaidAmount * -1) AS Credit, NULL,BillID,-1 AS
Flag
FROM tblAccountStatus
WHERE tblAccountStatus.OwnerID=Forms!frmBillStatement!cbOwnerName;

---------------------------------------------------------
Function Dues(tb3Months0 As Double, tb2Months0 As Currency, tb1Month0 As
Currency, tbCurrent0 As Currency, months As Integer) As Currency
Dim tb3Months As Currency, tb2Months As Currency, tb1Month As Currency,
tbCurrent As Currency
Dim diff As Double

diff = 0
tb3Months = tb3Months0
tb2Months = tb2Months0
tb1Month = tb1Month0
tbCurrent = tbCurrent0

If tb3Months > 0 Then
If tb2Months < 0 Then
diff = tb3Months + tb2Months
tb3Months = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb2Months > 0 Then
diff = tb3Months + tb2Months
tb2Months = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then
If tb1Month < 0 Then
diff = tb3Months + tb1Month
tb3Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb1Month > 0 Then
diff = tb3Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb3Months > 0 Then
If tbCurrent < 0 Then
diff = tb3Months + tbCurrent
tb3Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tbCurrent > 0 Then
diff = tb3Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then
If tb1Month < 0 Then
diff = tb2Months + tb1Month
tb2Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tb1Month > 0 Then
diff = tb2Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb2Months > 0 Then
If tbCurrent < 0 Then
diff = tb2Months + tbCurrent
tb2Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tbCurrent > 0 Then
diff = tb2Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If

If tb1Month > 0 Then
If tbCurrent < 0 Then
diff = tb1Month + tbCurrent
tb1Month = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb1Month < 0 Then
If tbCurrent > 0 Then
diff = tb1Month + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
End If

Select Case months
Case 3: Dues = tb3Months
Case 2: Dues = tb2Months
Case 1: Dues = tb1Month
Case 0: Dues = tbCurrent
End Select
End Function
 
B

Bob Vance

Thanks Dirk, That worked, except I had already pasted 1045 records by hand
:) :)..Regards Bob
 
B

Bob Vance

Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob
 
B

Bob Vance

Ooops Find and Replace........Duur Bob
Bob Vance said:
Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob

Bob Vance said:
Thanks Dirk, That worked, except I had already pasted 1045 records by
hand :) :)..Regards Bob
Click to expand...
Click to expand...
 
D

Douglas J. Steele

No. You're dealing with a database. Use an Update query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Bob Vance said:
Ooops Find and Replace........Duur Bob
Bob Vance said:
Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob

Bob Vance said:
Thanks Dirk, That worked, except I had already pasted 1045 records by
hand :) :)..Regards Bob

I have added a new field to my table now I have to put the same date in
every record is there and easier way other than pasting in each record!


Run an update query. You can build an update query in the query design
grid -- you have to change the query type from the default -- or you
can build it in SQL view, where it would look vaguely like this:

UPDATE YourTable SET YourField = #9/15/2008#;

You only need to run the query once to update all existing records, and
you don't have to save it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
Click to expand...
 
M

Midsomer

We have to do this, too, and copy the field to Excel where the Fill
feature will put the same value in an entire column. Then copy the
column of new data and paste it into the column in the Access table.

To quickly fill a column of data in Excel, enter the date in the top
cell and then double click the cell's Fill Handle

Midsomer
 
D

Douglas J. Steele

Why on earth go to that effort when a simple Update query will do it for you
all in Access?
 
J

John W. Vinson

Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob
Click to expand...

Specific:

UPDATE [tblHorseInfo] SET [Sex] = Left([Sex], 1)

More general, not dependent on the update being to the first character of a
string:

UPDATE [tblHorseInfo] SET [Sex] = Switch([Sex] = "Colt", "C", [Sex] =
"Gelding", "G", [Sex] = "Filly", "F", [Sex] = "Mare", "M", True, [Sex])

The last bit with the True sets any value not in the list back to itself -
i.e. if there is a "Stallion" in the table it will stay Stallion.

Just out of curiosity, I would think that the sex of a horse is stable; a
filly and a mare are both female, and a filly (when she's bred) will become a
mare. Is it really appropriate to have the sex field used for what are
basically two different attributes of the animal?

Also... doesn't a colt have a gender? or does the name colt imply maleness?
 
B

Bob Vance

Thanks John, female horse is born filly then at 4 becomes a mare, a male is
born a colt then at 4 becomes a horse/stallion, then if he has the
misfortune of losing 2 stone he becomes a gelding like me :) :) ... Bob
John W. Vinson said:
Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob
Click to expand...

Specific:

UPDATE [tblHorseInfo] SET [Sex] = Left([Sex], 1)

More general, not dependent on the update being to the first character of
a
string:

UPDATE [tblHorseInfo] SET [Sex] = Switch([Sex] = "Colt", "C", [Sex] =
"Gelding", "G", [Sex] = "Filly", "F", [Sex] = "Mare", "M", True, [Sex])

The last bit with the True sets any value not in the list back to itself -
i.e. if there is a "Stallion" in the table it will stay Stallion.

Just out of curiosity, I would think that the sex of a horse is stable; a
filly and a mare are both female, and a filly (when she's bred) will
become a
mare. Is it really appropriate to have the sex field used for what are
basically two different attributes of the animal?

Also... doesn't a colt have a gender? or does the name colt imply
maleness?
Click to expand...
 
B

Bob Vance

Ok John sorry miss read your post
[Is it really appropriate to have the sex field used for what are
basically two different attributes of the animal?] the male Colt/Gelding
means the same horse is worth 50 Million or 50 dollars as the kids ridding
horse :) :)


John W. Vinson said:
Dirk is it possible to update query 4 things in a field like
[TblHorseInfo],Field [Sex]
Colt to C
Gelding to G
Filly to F
Mare to M
Thanks Bob
Click to expand...

Specific:

UPDATE [tblHorseInfo] SET [Sex] = Left([Sex], 1)

More general, not dependent on the update being to the first character of
a
string:

UPDATE [tblHorseInfo] SET [Sex] = Switch([Sex] = "Colt", "C", [Sex] =
"Gelding", "G", [Sex] = "Filly", "F", [Sex] = "Mare", "M", True, [Sex])

The last bit with the True sets any value not in the list back to itself -
i.e. if there is a "Stallion" in the table it will stay Stallion.

Just out of curiosity, I would think that the sex of a horse is stable; a
filly and a mare are both female, and a filly (when she's bred) will
become a
mare. Is it really appropriate to have the sex field used for what are
basically two different attributes of the animal?

Also... doesn't a colt have a gender? or does the name colt imply
maleness?
Click to expand...
 
Top